1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self):
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self):
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    @property
 242    def meta(self) -> t.Dict[str, t.Any]:
 243        if self._meta is None:
 244            self._meta = {}
 245        return self._meta
 246
 247    def __deepcopy__(self, memo):
 248        copy = self.__class__(**deepcopy(self.args))
 249        if self.comments is not None:
 250            copy.comments = deepcopy(self.comments)
 251
 252        if self._type is not None:
 253            copy._type = self._type.copy()
 254
 255        if self._meta is not None:
 256            copy._meta = deepcopy(self._meta)
 257
 258        return copy
 259
 260    def copy(self):
 261        """
 262        Returns a deep copy of the expression.
 263        """
 264        new = deepcopy(self)
 265        new.parent = self.parent
 266        return new
 267
 268    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 269        if self.comments is None:
 270            self.comments = []
 271        if comments:
 272            for comment in comments:
 273                _, *meta = comment.split(SQLGLOT_META)
 274                if meta:
 275                    for kv in "".join(meta).split(","):
 276                        k, *v = kv.split("=")
 277                        value = v[0].strip() if v else True
 278                        self.meta[k.strip()] = value
 279                self.comments.append(comment)
 280
 281    def append(self, arg_key: str, value: t.Any) -> None:
 282        """
 283        Appends value to arg_key if it's a list or sets it as a new list.
 284
 285        Args:
 286            arg_key (str): name of the list expression arg
 287            value (Any): value to append to the list
 288        """
 289        if not isinstance(self.args.get(arg_key), list):
 290            self.args[arg_key] = []
 291        self.args[arg_key].append(value)
 292        self._set_parent(arg_key, value)
 293
 294    def set(self, arg_key: str, value: t.Any) -> None:
 295        """
 296        Sets arg_key to value.
 297
 298        Args:
 299            arg_key: name of the expression arg.
 300            value: value to set the arg to.
 301        """
 302        if value is None:
 303            self.args.pop(arg_key, None)
 304            return
 305
 306        self.args[arg_key] = value
 307        self._set_parent(arg_key, value)
 308
 309    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 310        if hasattr(value, "parent"):
 311            value.parent = self
 312            value.arg_key = arg_key
 313        elif type(value) is list:
 314            for v in value:
 315                if hasattr(v, "parent"):
 316                    v.parent = self
 317                    v.arg_key = arg_key
 318
 319    @property
 320    def depth(self) -> int:
 321        """
 322        Returns the depth of this tree.
 323        """
 324        if self.parent:
 325            return self.parent.depth + 1
 326        return 0
 327
 328    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 329        """Yields the key and expression for all arguments, exploding list args."""
 330        for k, vs in self.args.items():
 331            if type(vs) is list:
 332                for v in vs:
 333                    if hasattr(v, "parent"):
 334                        yield k, v
 335            else:
 336                if hasattr(vs, "parent"):
 337                    yield k, vs
 338
 339    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 340        """
 341        Returns the first node in this tree which matches at least one of
 342        the specified types.
 343
 344        Args:
 345            expression_types: the expression type(s) to match.
 346            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 347
 348        Returns:
 349            The node which matches the criteria or None if no such node was found.
 350        """
 351        return next(self.find_all(*expression_types, bfs=bfs), None)
 352
 353    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 354        """
 355        Returns a generator object which visits all nodes in this tree and only
 356        yields those that match at least one of the specified expression types.
 357
 358        Args:
 359            expression_types: the expression type(s) to match.
 360            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 361
 362        Returns:
 363            The generator object.
 364        """
 365        for expression, *_ in self.walk(bfs=bfs):
 366            if isinstance(expression, expression_types):
 367                yield expression
 368
 369    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 370        """
 371        Returns a nearest parent matching expression_types.
 372
 373        Args:
 374            expression_types: the expression type(s) to match.
 375
 376        Returns:
 377            The parent node.
 378        """
 379        ancestor = self.parent
 380        while ancestor and not isinstance(ancestor, expression_types):
 381            ancestor = ancestor.parent
 382        return t.cast(E, ancestor)
 383
 384    @property
 385    def parent_select(self) -> t.Optional[Select]:
 386        """
 387        Returns the parent select statement.
 388        """
 389        return self.find_ancestor(Select)
 390
 391    @property
 392    def same_parent(self) -> bool:
 393        """Returns if the parent is the same class as itself."""
 394        return type(self.parent) is self.__class__
 395
 396    def root(self) -> Expression:
 397        """
 398        Returns the root expression of this tree.
 399        """
 400        expression = self
 401        while expression.parent:
 402            expression = expression.parent
 403        return expression
 404
 405    def walk(self, bfs=True, prune=None):
 406        """
 407        Returns a generator object which visits all nodes in this tree.
 408
 409        Args:
 410            bfs (bool): if set to True the BFS traversal order will be applied,
 411                otherwise the DFS traversal will be used instead.
 412            prune ((node, parent, arg_key) -> bool): callable that returns True if
 413                the generator should stop traversing this branch of the tree.
 414
 415        Returns:
 416            the generator object.
 417        """
 418        if bfs:
 419            yield from self.bfs(prune=prune)
 420        else:
 421            yield from self.dfs(prune=prune)
 422
 423    def dfs(self, parent=None, key=None, prune=None):
 424        """
 425        Returns a generator object which visits all nodes in this tree in
 426        the DFS (Depth-first) order.
 427
 428        Returns:
 429            The generator object.
 430        """
 431        parent = parent or self.parent
 432        yield self, parent, key
 433        if prune and prune(self, parent, key):
 434            return
 435
 436        for k, v in self.iter_expressions():
 437            yield from v.dfs(self, k, prune)
 438
 439    def bfs(self, prune=None):
 440        """
 441        Returns a generator object which visits all nodes in this tree in
 442        the BFS (Breadth-first) order.
 443
 444        Returns:
 445            The generator object.
 446        """
 447        queue = deque([(self, self.parent, None)])
 448
 449        while queue:
 450            item, parent, key = queue.popleft()
 451
 452            yield item, parent, key
 453            if prune and prune(item, parent, key):
 454                continue
 455
 456            for k, v in item.iter_expressions():
 457                queue.append((v, item, k))
 458
 459    def unnest(self):
 460        """
 461        Returns the first non parenthesis child or self.
 462        """
 463        expression = self
 464        while type(expression) is Paren:
 465            expression = expression.this
 466        return expression
 467
 468    def unalias(self):
 469        """
 470        Returns the inner expression if this is an Alias.
 471        """
 472        if isinstance(self, Alias):
 473            return self.this
 474        return self
 475
 476    def unnest_operands(self):
 477        """
 478        Returns unnested operands as a tuple.
 479        """
 480        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 481
 482    def flatten(self, unnest=True):
 483        """
 484        Returns a generator which yields child nodes who's parents are the same class.
 485
 486        A AND B AND C -> [A, B, C]
 487        """
 488        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 489            if not type(node) is self.__class__:
 490                yield node.unnest() if unnest else node
 491
 492    def __str__(self) -> str:
 493        return self.sql()
 494
 495    def __repr__(self) -> str:
 496        return self._to_s()
 497
 498    def sql(self, dialect: DialectType = None, **opts) -> str:
 499        """
 500        Returns SQL string representation of this tree.
 501
 502        Args:
 503            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 504            opts: other `sqlglot.generator.Generator` options.
 505
 506        Returns:
 507            The SQL string.
 508        """
 509        from sqlglot.dialects import Dialect
 510
 511        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 512
 513    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 514        indent = "" if not level else "\n"
 515        indent += "".join(["  "] * level)
 516        left = f"({self.key.upper()} "
 517
 518        args: t.Dict[str, t.Any] = {
 519            k: ", ".join(
 520                v._to_s(hide_missing=hide_missing, level=level + 1)
 521                if hasattr(v, "_to_s")
 522                else str(v)
 523                for v in ensure_list(vs)
 524                if v is not None
 525            )
 526            for k, vs in self.args.items()
 527        }
 528        args["comments"] = self.comments
 529        args["type"] = self.type
 530        args = {k: v for k, v in args.items() if v or not hide_missing}
 531
 532        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 533        right += ")"
 534
 535        return indent + left + right
 536
 537    def transform(self, fun, *args, copy=True, **kwargs):
 538        """
 539        Recursively visits all tree nodes (excluding already transformed ones)
 540        and applies the given transformation function to each node.
 541
 542        Args:
 543            fun (function): a function which takes a node as an argument and returns a
 544                new transformed node or the same node without modifications. If the function
 545                returns None, then the corresponding node will be removed from the syntax tree.
 546            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 547                modified in place.
 548
 549        Returns:
 550            The transformed tree.
 551        """
 552        node = self.copy() if copy else self
 553        new_node = fun(node, *args, **kwargs)
 554
 555        if new_node is None or not isinstance(new_node, Expression):
 556            return new_node
 557        if new_node is not node:
 558            new_node.parent = node.parent
 559            return new_node
 560
 561        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 562        return new_node
 563
 564    @t.overload
 565    def replace(self, expression: E) -> E:
 566        ...
 567
 568    @t.overload
 569    def replace(self, expression: None) -> None:
 570        ...
 571
 572    def replace(self, expression):
 573        """
 574        Swap out this expression with a new expression.
 575
 576        For example::
 577
 578            >>> tree = Select().select("x").from_("tbl")
 579            >>> tree.find(Column).replace(Column(this="y"))
 580            (COLUMN this: y)
 581            >>> tree.sql()
 582            'SELECT y FROM tbl'
 583
 584        Args:
 585            expression: new node
 586
 587        Returns:
 588            The new expression or expressions.
 589        """
 590        if not self.parent:
 591            return expression
 592
 593        parent = self.parent
 594        self.parent = None
 595
 596        replace_children(parent, lambda child: expression if child is self else child)
 597        return expression
 598
 599    def pop(self: E) -> E:
 600        """
 601        Remove this expression from its AST.
 602
 603        Returns:
 604            The popped expression.
 605        """
 606        self.replace(None)
 607        return self
 608
 609    def assert_is(self, type_: t.Type[E]) -> E:
 610        """
 611        Assert that this `Expression` is an instance of `type_`.
 612
 613        If it is NOT an instance of `type_`, this raises an assertion error.
 614        Otherwise, this returns this expression.
 615
 616        Examples:
 617            This is useful for type security in chained expressions:
 618
 619            >>> import sqlglot
 620            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 621            'SELECT x, z FROM y'
 622        """
 623        assert isinstance(self, type_)
 624        return self
 625
 626    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 627        """
 628        Checks if this expression is valid (e.g. all mandatory args are set).
 629
 630        Args:
 631            args: a sequence of values that were used to instantiate a Func expression. This is used
 632                to check that the provided arguments don't exceed the function argument limit.
 633
 634        Returns:
 635            A list of error messages for all possible errors that were found.
 636        """
 637        errors: t.List[str] = []
 638
 639        for k in self.args:
 640            if k not in self.arg_types:
 641                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 642        for k, mandatory in self.arg_types.items():
 643            v = self.args.get(k)
 644            if mandatory and (v is None or (isinstance(v, list) and not v)):
 645                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 646
 647        if (
 648            args
 649            and isinstance(self, Func)
 650            and len(args) > len(self.arg_types)
 651            and not self.is_var_len_args
 652        ):
 653            errors.append(
 654                f"The number of provided arguments ({len(args)}) is greater than "
 655                f"the maximum number of supported arguments ({len(self.arg_types)})"
 656            )
 657
 658        return errors
 659
 660    def dump(self):
 661        """
 662        Dump this Expression to a JSON-serializable dict.
 663        """
 664        from sqlglot.serde import dump
 665
 666        return dump(self)
 667
 668    @classmethod
 669    def load(cls, obj):
 670        """
 671        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 672        """
 673        from sqlglot.serde import load
 674
 675        return load(obj)
 676
 677    def and_(
 678        self,
 679        *expressions: t.Optional[ExpOrStr],
 680        dialect: DialectType = None,
 681        copy: bool = True,
 682        **opts,
 683    ) -> Condition:
 684        """
 685        AND this condition with one or multiple expressions.
 686
 687        Example:
 688            >>> condition("x=1").and_("y=1").sql()
 689            'x = 1 AND y = 1'
 690
 691        Args:
 692            *expressions: the SQL code strings to parse.
 693                If an `Expression` instance is passed, it will be used as-is.
 694            dialect: the dialect used to parse the input expression.
 695            copy: whether or not to copy the involved expressions (only applies to Expressions).
 696            opts: other options to use to parse the input expressions.
 697
 698        Returns:
 699            The new And condition.
 700        """
 701        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 702
 703    def or_(
 704        self,
 705        *expressions: t.Optional[ExpOrStr],
 706        dialect: DialectType = None,
 707        copy: bool = True,
 708        **opts,
 709    ) -> Condition:
 710        """
 711        OR this condition with one or multiple expressions.
 712
 713        Example:
 714            >>> condition("x=1").or_("y=1").sql()
 715            'x = 1 OR y = 1'
 716
 717        Args:
 718            *expressions: the SQL code strings to parse.
 719                If an `Expression` instance is passed, it will be used as-is.
 720            dialect: the dialect used to parse the input expression.
 721            copy: whether or not to copy the involved expressions (only applies to Expressions).
 722            opts: other options to use to parse the input expressions.
 723
 724        Returns:
 725            The new Or condition.
 726        """
 727        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 728
 729    def not_(self, copy: bool = True):
 730        """
 731        Wrap this condition with NOT.
 732
 733        Example:
 734            >>> condition("x=1").not_().sql()
 735            'NOT x = 1'
 736
 737        Args:
 738            copy: whether or not to copy this object.
 739
 740        Returns:
 741            The new Not instance.
 742        """
 743        return not_(self, copy=copy)
 744
 745    def as_(
 746        self,
 747        alias: str | Identifier,
 748        quoted: t.Optional[bool] = None,
 749        dialect: DialectType = None,
 750        copy: bool = True,
 751        **opts,
 752    ) -> Alias:
 753        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 754
 755    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 756        this = self.copy()
 757        other = convert(other, copy=True)
 758        if not isinstance(this, klass) and not isinstance(other, klass):
 759            this = _wrap(this, Binary)
 760            other = _wrap(other, Binary)
 761        if reverse:
 762            return klass(this=other, expression=this)
 763        return klass(this=this, expression=other)
 764
 765    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 766        return Bracket(
 767            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 768        )
 769
 770    def __iter__(self) -> t.Iterator:
 771        if "expressions" in self.arg_types:
 772            return iter(self.args.get("expressions") or [])
 773        # We define this because __getitem__ converts Expression into an iterable, which is
 774        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 775        # See: https://peps.python.org/pep-0234/
 776        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 777
 778    def isin(
 779        self,
 780        *expressions: t.Any,
 781        query: t.Optional[ExpOrStr] = None,
 782        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 783        copy: bool = True,
 784        **opts,
 785    ) -> In:
 786        return In(
 787            this=maybe_copy(self, copy),
 788            expressions=[convert(e, copy=copy) for e in expressions],
 789            query=maybe_parse(query, copy=copy, **opts) if query else None,
 790            unnest=Unnest(
 791                expressions=[
 792                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 793                ]
 794            )
 795            if unnest
 796            else None,
 797        )
 798
 799    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 800        return Between(
 801            this=maybe_copy(self, copy),
 802            low=convert(low, copy=copy, **opts),
 803            high=convert(high, copy=copy, **opts),
 804        )
 805
 806    def is_(self, other: ExpOrStr) -> Is:
 807        return self._binop(Is, other)
 808
 809    def like(self, other: ExpOrStr) -> Like:
 810        return self._binop(Like, other)
 811
 812    def ilike(self, other: ExpOrStr) -> ILike:
 813        return self._binop(ILike, other)
 814
 815    def eq(self, other: t.Any) -> EQ:
 816        return self._binop(EQ, other)
 817
 818    def neq(self, other: t.Any) -> NEQ:
 819        return self._binop(NEQ, other)
 820
 821    def rlike(self, other: ExpOrStr) -> RegexpLike:
 822        return self._binop(RegexpLike, other)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Subqueryable) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Unionable(Expression):
 924    def union(
 925        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 926    ) -> Unionable:
 927        """
 928        Builds a UNION expression.
 929
 930        Example:
 931            >>> import sqlglot
 932            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 933            'SELECT * FROM foo UNION SELECT * FROM bla'
 934
 935        Args:
 936            expression: the SQL code string.
 937                If an `Expression` instance is passed, it will be used as-is.
 938            distinct: set the DISTINCT flag if and only if this is true.
 939            dialect: the dialect used to parse the input expression.
 940            opts: other options to use to parse the input expressions.
 941
 942        Returns:
 943            The new Union expression.
 944        """
 945        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 946
 947    def intersect(
 948        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 949    ) -> Unionable:
 950        """
 951        Builds an INTERSECT expression.
 952
 953        Example:
 954            >>> import sqlglot
 955            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 956            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 957
 958        Args:
 959            expression: the SQL code string.
 960                If an `Expression` instance is passed, it will be used as-is.
 961            distinct: set the DISTINCT flag if and only if this is true.
 962            dialect: the dialect used to parse the input expression.
 963            opts: other options to use to parse the input expressions.
 964
 965        Returns:
 966            The new Intersect expression.
 967        """
 968        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 969
 970    def except_(
 971        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 972    ) -> Unionable:
 973        """
 974        Builds an EXCEPT expression.
 975
 976        Example:
 977            >>> import sqlglot
 978            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 979            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 980
 981        Args:
 982            expression: the SQL code string.
 983                If an `Expression` instance is passed, it will be used as-is.
 984            distinct: set the DISTINCT flag if and only if this is true.
 985            dialect: the dialect used to parse the input expression.
 986            opts: other options to use to parse the input expressions.
 987
 988        Returns:
 989            The new Except expression.
 990        """
 991        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 992
 993
 994class UDTF(DerivedTable, Unionable):
 995    @property
 996    def selects(self) -> t.List[Expression]:
 997        alias = self.args.get("alias")
 998        return alias.columns if alias else []
 999
1000
1001class Cache(Expression):
1002    arg_types = {
1003        "with": False,
1004        "this": True,
1005        "lazy": False,
1006        "options": False,
1007        "expression": False,
1008    }
1009
1010
1011class Uncache(Expression):
1012    arg_types = {"this": True, "exists": False}
1013
1014
1015class DDL(Expression):
1016    @property
1017    def ctes(self):
1018        with_ = self.args.get("with")
1019        if not with_:
1020            return []
1021        return with_.expressions
1022
1023    @property
1024    def named_selects(self) -> t.List[str]:
1025        if isinstance(self.expression, Subqueryable):
1026            return self.expression.named_selects
1027        return []
1028
1029    @property
1030    def selects(self) -> t.List[Expression]:
1031        if isinstance(self.expression, Subqueryable):
1032            return self.expression.selects
1033        return []
1034
1035
1036class Create(DDL):
1037    arg_types = {
1038        "with": False,
1039        "this": True,
1040        "kind": True,
1041        "expression": False,
1042        "exists": False,
1043        "properties": False,
1044        "replace": False,
1045        "unique": False,
1046        "indexes": False,
1047        "no_schema_binding": False,
1048        "begin": False,
1049        "end": False,
1050        "clone": False,
1051    }
1052
1053
1054# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1055# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1056# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1057class Clone(Expression):
1058    arg_types = {
1059        "this": True,
1060        "when": False,
1061        "kind": False,
1062        "shallow": False,
1063        "expression": False,
1064        "copy": False,
1065    }
1066
1067
1068class Describe(Expression):
1069    arg_types = {"this": True, "kind": False, "expressions": False}
1070
1071
1072class Kill(Expression):
1073    arg_types = {"this": True, "kind": False}
1074
1075
1076class Pragma(Expression):
1077    pass
1078
1079
1080class Set(Expression):
1081    arg_types = {"expressions": False, "unset": False, "tag": False}
1082
1083
1084class SetItem(Expression):
1085    arg_types = {
1086        "this": False,
1087        "expressions": False,
1088        "kind": False,
1089        "collate": False,  # MySQL SET NAMES statement
1090        "global": False,
1091    }
1092
1093
1094class Show(Expression):
1095    arg_types = {
1096        "this": True,
1097        "target": False,
1098        "offset": False,
1099        "limit": False,
1100        "like": False,
1101        "where": False,
1102        "db": False,
1103        "scope": False,
1104        "scope_kind": False,
1105        "full": False,
1106        "mutex": False,
1107        "query": False,
1108        "channel": False,
1109        "global": False,
1110        "log": False,
1111        "position": False,
1112        "types": False,
1113    }
1114
1115
1116class UserDefinedFunction(Expression):
1117    arg_types = {"this": True, "expressions": False, "wrapped": False}
1118
1119
1120class CharacterSet(Expression):
1121    arg_types = {"this": True, "default": False}
1122
1123
1124class With(Expression):
1125    arg_types = {"expressions": True, "recursive": False}
1126
1127    @property
1128    def recursive(self) -> bool:
1129        return bool(self.args.get("recursive"))
1130
1131
1132class WithinGroup(Expression):
1133    arg_types = {"this": True, "expression": False}
1134
1135
1136class CTE(DerivedTable):
1137    arg_types = {"this": True, "alias": True}
1138
1139
1140class TableAlias(Expression):
1141    arg_types = {"this": False, "columns": False}
1142
1143    @property
1144    def columns(self):
1145        return self.args.get("columns") or []
1146
1147
1148class BitString(Condition):
1149    pass
1150
1151
1152class HexString(Condition):
1153    pass
1154
1155
1156class ByteString(Condition):
1157    pass
1158
1159
1160class RawString(Condition):
1161    pass
1162
1163
1164class Column(Condition):
1165    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1166
1167    @property
1168    def table(self) -> str:
1169        return self.text("table")
1170
1171    @property
1172    def db(self) -> str:
1173        return self.text("db")
1174
1175    @property
1176    def catalog(self) -> str:
1177        return self.text("catalog")
1178
1179    @property
1180    def output_name(self) -> str:
1181        return self.name
1182
1183    @property
1184    def parts(self) -> t.List[Identifier]:
1185        """Return the parts of a column in order catalog, db, table, name."""
1186        return [
1187            t.cast(Identifier, self.args[part])
1188            for part in ("catalog", "db", "table", "this")
1189            if self.args.get(part)
1190        ]
1191
1192    def to_dot(self) -> Dot | Identifier:
1193        """Converts the column into a dot expression."""
1194        parts = self.parts
1195        parent = self.parent
1196
1197        while parent:
1198            if isinstance(parent, Dot):
1199                parts.append(parent.expression)
1200            parent = parent.parent
1201
1202        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1203
1204
1205class ColumnPosition(Expression):
1206    arg_types = {"this": False, "position": True}
1207
1208
1209class ColumnDef(Expression):
1210    arg_types = {
1211        "this": True,
1212        "kind": False,
1213        "constraints": False,
1214        "exists": False,
1215        "position": False,
1216    }
1217
1218    @property
1219    def constraints(self) -> t.List[ColumnConstraint]:
1220        return self.args.get("constraints") or []
1221
1222
1223class AlterColumn(Expression):
1224    arg_types = {
1225        "this": True,
1226        "dtype": False,
1227        "collate": False,
1228        "using": False,
1229        "default": False,
1230        "drop": False,
1231    }
1232
1233
1234class RenameTable(Expression):
1235    pass
1236
1237
1238class Comment(Expression):
1239    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1240
1241
1242class Comprehension(Expression):
1243    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1244
1245
1246# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1247class MergeTreeTTLAction(Expression):
1248    arg_types = {
1249        "this": True,
1250        "delete": False,
1251        "recompress": False,
1252        "to_disk": False,
1253        "to_volume": False,
1254    }
1255
1256
1257# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1258class MergeTreeTTL(Expression):
1259    arg_types = {
1260        "expressions": True,
1261        "where": False,
1262        "group": False,
1263        "aggregates": False,
1264    }
1265
1266
1267# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1268class IndexConstraintOption(Expression):
1269    arg_types = {
1270        "key_block_size": False,
1271        "using": False,
1272        "parser": False,
1273        "comment": False,
1274        "visible": False,
1275        "engine_attr": False,
1276        "secondary_engine_attr": False,
1277    }
1278
1279
1280class ColumnConstraint(Expression):
1281    arg_types = {"this": False, "kind": True}
1282
1283    @property
1284    def kind(self) -> ColumnConstraintKind:
1285        return self.args["kind"]
1286
1287
1288class ColumnConstraintKind(Expression):
1289    pass
1290
1291
1292class AutoIncrementColumnConstraint(ColumnConstraintKind):
1293    pass
1294
1295
1296class CaseSpecificColumnConstraint(ColumnConstraintKind):
1297    arg_types = {"not_": True}
1298
1299
1300class CharacterSetColumnConstraint(ColumnConstraintKind):
1301    arg_types = {"this": True}
1302
1303
1304class CheckColumnConstraint(ColumnConstraintKind):
1305    pass
1306
1307
1308class ClusteredColumnConstraint(ColumnConstraintKind):
1309    pass
1310
1311
1312class CollateColumnConstraint(ColumnConstraintKind):
1313    pass
1314
1315
1316class CommentColumnConstraint(ColumnConstraintKind):
1317    pass
1318
1319
1320class CompressColumnConstraint(ColumnConstraintKind):
1321    pass
1322
1323
1324class DateFormatColumnConstraint(ColumnConstraintKind):
1325    arg_types = {"this": True}
1326
1327
1328class DefaultColumnConstraint(ColumnConstraintKind):
1329    pass
1330
1331
1332class EncodeColumnConstraint(ColumnConstraintKind):
1333    pass
1334
1335
1336class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1337    # this: True -> ALWAYS, this: False -> BY DEFAULT
1338    arg_types = {
1339        "this": False,
1340        "expression": False,
1341        "on_null": False,
1342        "start": False,
1343        "increment": False,
1344        "minvalue": False,
1345        "maxvalue": False,
1346        "cycle": False,
1347    }
1348
1349
1350# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1351class IndexColumnConstraint(ColumnConstraintKind):
1352    arg_types = {
1353        "this": False,
1354        "schema": True,
1355        "kind": False,
1356        "index_type": False,
1357        "options": False,
1358    }
1359
1360
1361class InlineLengthColumnConstraint(ColumnConstraintKind):
1362    pass
1363
1364
1365class NonClusteredColumnConstraint(ColumnConstraintKind):
1366    pass
1367
1368
1369class NotForReplicationColumnConstraint(ColumnConstraintKind):
1370    arg_types = {}
1371
1372
1373class NotNullColumnConstraint(ColumnConstraintKind):
1374    arg_types = {"allow_null": False}
1375
1376
1377# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1378class OnUpdateColumnConstraint(ColumnConstraintKind):
1379    pass
1380
1381
1382class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1383    arg_types = {"desc": False}
1384
1385
1386class TitleColumnConstraint(ColumnConstraintKind):
1387    pass
1388
1389
1390class UniqueColumnConstraint(ColumnConstraintKind):
1391    arg_types = {"this": False, "index_type": False}
1392
1393
1394class UppercaseColumnConstraint(ColumnConstraintKind):
1395    arg_types: t.Dict[str, t.Any] = {}
1396
1397
1398class PathColumnConstraint(ColumnConstraintKind):
1399    pass
1400
1401
1402# computed column expression
1403# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1404class ComputedColumnConstraint(ColumnConstraintKind):
1405    arg_types = {"this": True, "persisted": False, "not_null": False}
1406
1407
1408class Constraint(Expression):
1409    arg_types = {"this": True, "expressions": True}
1410
1411
1412class Delete(Expression):
1413    arg_types = {
1414        "with": False,
1415        "this": False,
1416        "using": False,
1417        "where": False,
1418        "returning": False,
1419        "limit": False,
1420        "tables": False,  # Multiple-Table Syntax (MySQL)
1421    }
1422
1423    def delete(
1424        self,
1425        table: ExpOrStr,
1426        dialect: DialectType = None,
1427        copy: bool = True,
1428        **opts,
1429    ) -> Delete:
1430        """
1431        Create a DELETE expression or replace the table on an existing DELETE expression.
1432
1433        Example:
1434            >>> delete("tbl").sql()
1435            'DELETE FROM tbl'
1436
1437        Args:
1438            table: the table from which to delete.
1439            dialect: the dialect used to parse the input expression.
1440            copy: if `False`, modify this expression instance in-place.
1441            opts: other options to use to parse the input expressions.
1442
1443        Returns:
1444            Delete: the modified expression.
1445        """
1446        return _apply_builder(
1447            expression=table,
1448            instance=self,
1449            arg="this",
1450            dialect=dialect,
1451            into=Table,
1452            copy=copy,
1453            **opts,
1454        )
1455
1456    def where(
1457        self,
1458        *expressions: t.Optional[ExpOrStr],
1459        append: bool = True,
1460        dialect: DialectType = None,
1461        copy: bool = True,
1462        **opts,
1463    ) -> Delete:
1464        """
1465        Append to or set the WHERE expressions.
1466
1467        Example:
1468            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1469            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1470
1471        Args:
1472            *expressions: the SQL code strings to parse.
1473                If an `Expression` instance is passed, it will be used as-is.
1474                Multiple expressions are combined with an AND operator.
1475            append: if `True`, AND the new expressions to any existing expression.
1476                Otherwise, this resets the expression.
1477            dialect: the dialect used to parse the input expressions.
1478            copy: if `False`, modify this expression instance in-place.
1479            opts: other options to use to parse the input expressions.
1480
1481        Returns:
1482            Delete: the modified expression.
1483        """
1484        return _apply_conjunction_builder(
1485            *expressions,
1486            instance=self,
1487            arg="where",
1488            append=append,
1489            into=Where,
1490            dialect=dialect,
1491            copy=copy,
1492            **opts,
1493        )
1494
1495    def returning(
1496        self,
1497        expression: ExpOrStr,
1498        dialect: DialectType = None,
1499        copy: bool = True,
1500        **opts,
1501    ) -> Delete:
1502        """
1503        Set the RETURNING expression. Not supported by all dialects.
1504
1505        Example:
1506            >>> delete("tbl").returning("*", dialect="postgres").sql()
1507            'DELETE FROM tbl RETURNING *'
1508
1509        Args:
1510            expression: the SQL code strings to parse.
1511                If an `Expression` instance is passed, it will be used as-is.
1512            dialect: the dialect used to parse the input expressions.
1513            copy: if `False`, modify this expression instance in-place.
1514            opts: other options to use to parse the input expressions.
1515
1516        Returns:
1517            Delete: the modified expression.
1518        """
1519        return _apply_builder(
1520            expression=expression,
1521            instance=self,
1522            arg="returning",
1523            prefix="RETURNING",
1524            dialect=dialect,
1525            copy=copy,
1526            into=Returning,
1527            **opts,
1528        )
1529
1530
1531class Drop(Expression):
1532    arg_types = {
1533        "this": False,
1534        "kind": False,
1535        "exists": False,
1536        "temporary": False,
1537        "materialized": False,
1538        "cascade": False,
1539        "constraints": False,
1540        "purge": False,
1541    }
1542
1543
1544class Filter(Expression):
1545    arg_types = {"this": True, "expression": True}
1546
1547
1548class Check(Expression):
1549    pass
1550
1551
1552# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1553class Connect(Expression):
1554    arg_types = {"start": False, "connect": True}
1555
1556
1557class Prior(Expression):
1558    pass
1559
1560
1561class Directory(Expression):
1562    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1563    arg_types = {"this": True, "local": False, "row_format": False}
1564
1565
1566class ForeignKey(Expression):
1567    arg_types = {
1568        "expressions": True,
1569        "reference": False,
1570        "delete": False,
1571        "update": False,
1572    }
1573
1574
1575class ColumnPrefix(Expression):
1576    arg_types = {"this": True, "expression": True}
1577
1578
1579class PrimaryKey(Expression):
1580    arg_types = {"expressions": True, "options": False}
1581
1582
1583# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1584# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1585class Into(Expression):
1586    arg_types = {"this": True, "temporary": False, "unlogged": False}
1587
1588
1589class From(Expression):
1590    @property
1591    def name(self) -> str:
1592        return self.this.name
1593
1594    @property
1595    def alias_or_name(self) -> str:
1596        return self.this.alias_or_name
1597
1598
1599class Having(Expression):
1600    pass
1601
1602
1603class Hint(Expression):
1604    arg_types = {"expressions": True}
1605
1606
1607class JoinHint(Expression):
1608    arg_types = {"this": True, "expressions": True}
1609
1610
1611class Identifier(Expression):
1612    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1613
1614    @property
1615    def quoted(self) -> bool:
1616        return bool(self.args.get("quoted"))
1617
1618    @property
1619    def hashable_args(self) -> t.Any:
1620        return (self.this, self.quoted)
1621
1622    @property
1623    def output_name(self) -> str:
1624        return self.name
1625
1626
1627# https://www.postgresql.org/docs/current/indexes-opclass.html
1628class Opclass(Expression):
1629    arg_types = {"this": True, "expression": True}
1630
1631
1632class Index(Expression):
1633    arg_types = {
1634        "this": False,
1635        "table": False,
1636        "using": False,
1637        "where": False,
1638        "columns": False,
1639        "unique": False,
1640        "primary": False,
1641        "amp": False,  # teradata
1642        "partition_by": False,  # teradata
1643        "where": False,  # postgres partial indexes
1644    }
1645
1646
1647class Insert(DDL):
1648    arg_types = {
1649        "with": False,
1650        "this": True,
1651        "expression": False,
1652        "conflict": False,
1653        "returning": False,
1654        "overwrite": False,
1655        "exists": False,
1656        "partition": False,
1657        "alternative": False,
1658        "where": False,
1659        "ignore": False,
1660        "by_name": False,
1661    }
1662
1663    def with_(
1664        self,
1665        alias: ExpOrStr,
1666        as_: ExpOrStr,
1667        recursive: t.Optional[bool] = None,
1668        append: bool = True,
1669        dialect: DialectType = None,
1670        copy: bool = True,
1671        **opts,
1672    ) -> Insert:
1673        """
1674        Append to or set the common table expressions.
1675
1676        Example:
1677            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1678            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1679
1680        Args:
1681            alias: the SQL code string to parse as the table name.
1682                If an `Expression` instance is passed, this is used as-is.
1683            as_: the SQL code string to parse as the table expression.
1684                If an `Expression` instance is passed, it will be used as-is.
1685            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1686            append: if `True`, add to any existing expressions.
1687                Otherwise, this resets the expressions.
1688            dialect: the dialect used to parse the input expression.
1689            copy: if `False`, modify this expression instance in-place.
1690            opts: other options to use to parse the input expressions.
1691
1692        Returns:
1693            The modified expression.
1694        """
1695        return _apply_cte_builder(
1696            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1697        )
1698
1699
1700class OnConflict(Expression):
1701    arg_types = {
1702        "duplicate": False,
1703        "expressions": False,
1704        "nothing": False,
1705        "key": False,
1706        "constraint": False,
1707    }
1708
1709
1710class Returning(Expression):
1711    arg_types = {"expressions": True, "into": False}
1712
1713
1714# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1715class Introducer(Expression):
1716    arg_types = {"this": True, "expression": True}
1717
1718
1719# national char, like n'utf8'
1720class National(Expression):
1721    pass
1722
1723
1724class LoadData(Expression):
1725    arg_types = {
1726        "this": True,
1727        "local": False,
1728        "overwrite": False,
1729        "inpath": True,
1730        "partition": False,
1731        "input_format": False,
1732        "serde": False,
1733    }
1734
1735
1736class Partition(Expression):
1737    arg_types = {"expressions": True}
1738
1739
1740class Fetch(Expression):
1741    arg_types = {
1742        "direction": False,
1743        "count": False,
1744        "percent": False,
1745        "with_ties": False,
1746    }
1747
1748
1749class Group(Expression):
1750    arg_types = {
1751        "expressions": False,
1752        "grouping_sets": False,
1753        "cube": False,
1754        "rollup": False,
1755        "totals": False,
1756        "all": False,
1757    }
1758
1759
1760class Lambda(Expression):
1761    arg_types = {"this": True, "expressions": True}
1762
1763
1764class Limit(Expression):
1765    arg_types = {"this": False, "expression": True, "offset": False}
1766
1767
1768class Literal(Condition):
1769    arg_types = {"this": True, "is_string": True}
1770
1771    @property
1772    def hashable_args(self) -> t.Any:
1773        return (self.this, self.args.get("is_string"))
1774
1775    @classmethod
1776    def number(cls, number) -> Literal:
1777        return cls(this=str(number), is_string=False)
1778
1779    @classmethod
1780    def string(cls, string) -> Literal:
1781        return cls(this=str(string), is_string=True)
1782
1783    @property
1784    def output_name(self) -> str:
1785        return self.name
1786
1787
1788class Join(Expression):
1789    arg_types = {
1790        "this": True,
1791        "on": False,
1792        "side": False,
1793        "kind": False,
1794        "using": False,
1795        "method": False,
1796        "global": False,
1797        "hint": False,
1798    }
1799
1800    @property
1801    def method(self) -> str:
1802        return self.text("method").upper()
1803
1804    @property
1805    def kind(self) -> str:
1806        return self.text("kind").upper()
1807
1808    @property
1809    def side(self) -> str:
1810        return self.text("side").upper()
1811
1812    @property
1813    def hint(self) -> str:
1814        return self.text("hint").upper()
1815
1816    @property
1817    def alias_or_name(self) -> str:
1818        return self.this.alias_or_name
1819
1820    def on(
1821        self,
1822        *expressions: t.Optional[ExpOrStr],
1823        append: bool = True,
1824        dialect: DialectType = None,
1825        copy: bool = True,
1826        **opts,
1827    ) -> Join:
1828        """
1829        Append to or set the ON expressions.
1830
1831        Example:
1832            >>> import sqlglot
1833            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1834            'JOIN x ON y = 1'
1835
1836        Args:
1837            *expressions: the SQL code strings to parse.
1838                If an `Expression` instance is passed, it will be used as-is.
1839                Multiple expressions are combined with an AND operator.
1840            append: if `True`, AND the new expressions to any existing expression.
1841                Otherwise, this resets the expression.
1842            dialect: the dialect used to parse the input expressions.
1843            copy: if `False`, modify this expression instance in-place.
1844            opts: other options to use to parse the input expressions.
1845
1846        Returns:
1847            The modified Join expression.
1848        """
1849        join = _apply_conjunction_builder(
1850            *expressions,
1851            instance=self,
1852            arg="on",
1853            append=append,
1854            dialect=dialect,
1855            copy=copy,
1856            **opts,
1857        )
1858
1859        if join.kind == "CROSS":
1860            join.set("kind", None)
1861
1862        return join
1863
1864    def using(
1865        self,
1866        *expressions: t.Optional[ExpOrStr],
1867        append: bool = True,
1868        dialect: DialectType = None,
1869        copy: bool = True,
1870        **opts,
1871    ) -> Join:
1872        """
1873        Append to or set the USING expressions.
1874
1875        Example:
1876            >>> import sqlglot
1877            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1878            'JOIN x USING (foo, bla)'
1879
1880        Args:
1881            *expressions: the SQL code strings to parse.
1882                If an `Expression` instance is passed, it will be used as-is.
1883            append: if `True`, concatenate the new expressions to the existing "using" list.
1884                Otherwise, this resets the expression.
1885            dialect: the dialect used to parse the input expressions.
1886            copy: if `False`, modify this expression instance in-place.
1887            opts: other options to use to parse the input expressions.
1888
1889        Returns:
1890            The modified Join expression.
1891        """
1892        join = _apply_list_builder(
1893            *expressions,
1894            instance=self,
1895            arg="using",
1896            append=append,
1897            dialect=dialect,
1898            copy=copy,
1899            **opts,
1900        )
1901
1902        if join.kind == "CROSS":
1903            join.set("kind", None)
1904
1905        return join
1906
1907
1908class Lateral(UDTF):
1909    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1910
1911
1912class MatchRecognize(Expression):
1913    arg_types = {
1914        "partition_by": False,
1915        "order": False,
1916        "measures": False,
1917        "rows": False,
1918        "after": False,
1919        "pattern": False,
1920        "define": False,
1921        "alias": False,
1922    }
1923
1924
1925# Clickhouse FROM FINAL modifier
1926# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1927class Final(Expression):
1928    pass
1929
1930
1931class Offset(Expression):
1932    arg_types = {"this": False, "expression": True}
1933
1934
1935class Order(Expression):
1936    arg_types = {"this": False, "expressions": True}
1937
1938
1939# hive specific sorts
1940# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1941class Cluster(Order):
1942    pass
1943
1944
1945class Distribute(Order):
1946    pass
1947
1948
1949class Sort(Order):
1950    pass
1951
1952
1953class Ordered(Expression):
1954    arg_types = {"this": True, "desc": False, "nulls_first": True}
1955
1956
1957class Property(Expression):
1958    arg_types = {"this": True, "value": True}
1959
1960
1961class AlgorithmProperty(Property):
1962    arg_types = {"this": True}
1963
1964
1965class AutoIncrementProperty(Property):
1966    arg_types = {"this": True}
1967
1968
1969class BlockCompressionProperty(Property):
1970    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1971
1972
1973class CharacterSetProperty(Property):
1974    arg_types = {"this": True, "default": True}
1975
1976
1977class ChecksumProperty(Property):
1978    arg_types = {"on": False, "default": False}
1979
1980
1981class CollateProperty(Property):
1982    arg_types = {"this": True}
1983
1984
1985class CopyGrantsProperty(Property):
1986    arg_types = {}
1987
1988
1989class DataBlocksizeProperty(Property):
1990    arg_types = {
1991        "size": False,
1992        "units": False,
1993        "minimum": False,
1994        "maximum": False,
1995        "default": False,
1996    }
1997
1998
1999class DefinerProperty(Property):
2000    arg_types = {"this": True}
2001
2002
2003class DistKeyProperty(Property):
2004    arg_types = {"this": True}
2005
2006
2007class DistStyleProperty(Property):
2008    arg_types = {"this": True}
2009
2010
2011class EngineProperty(Property):
2012    arg_types = {"this": True}
2013
2014
2015class HeapProperty(Property):
2016    arg_types = {}
2017
2018
2019class ToTableProperty(Property):
2020    arg_types = {"this": True}
2021
2022
2023class ExecuteAsProperty(Property):
2024    arg_types = {"this": True}
2025
2026
2027class ExternalProperty(Property):
2028    arg_types = {"this": False}
2029
2030
2031class FallbackProperty(Property):
2032    arg_types = {"no": True, "protection": False}
2033
2034
2035class FileFormatProperty(Property):
2036    arg_types = {"this": True}
2037
2038
2039class FreespaceProperty(Property):
2040    arg_types = {"this": True, "percent": False}
2041
2042
2043class InputOutputFormat(Expression):
2044    arg_types = {"input_format": False, "output_format": False}
2045
2046
2047class IsolatedLoadingProperty(Property):
2048    arg_types = {
2049        "no": True,
2050        "concurrent": True,
2051        "for_all": True,
2052        "for_insert": True,
2053        "for_none": True,
2054    }
2055
2056
2057class JournalProperty(Property):
2058    arg_types = {
2059        "no": False,
2060        "dual": False,
2061        "before": False,
2062        "local": False,
2063        "after": False,
2064    }
2065
2066
2067class LanguageProperty(Property):
2068    arg_types = {"this": True}
2069
2070
2071# spark ddl
2072class ClusteredByProperty(Property):
2073    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2074
2075
2076class DictProperty(Property):
2077    arg_types = {"this": True, "kind": True, "settings": False}
2078
2079
2080class DictSubProperty(Property):
2081    pass
2082
2083
2084class DictRange(Property):
2085    arg_types = {"this": True, "min": True, "max": True}
2086
2087
2088# Clickhouse CREATE ... ON CLUSTER modifier
2089# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2090class OnCluster(Property):
2091    arg_types = {"this": True}
2092
2093
2094class LikeProperty(Property):
2095    arg_types = {"this": True, "expressions": False}
2096
2097
2098class LocationProperty(Property):
2099    arg_types = {"this": True}
2100
2101
2102class LockingProperty(Property):
2103    arg_types = {
2104        "this": False,
2105        "kind": True,
2106        "for_or_in": True,
2107        "lock_type": True,
2108        "override": False,
2109    }
2110
2111
2112class LogProperty(Property):
2113    arg_types = {"no": True}
2114
2115
2116class MaterializedProperty(Property):
2117    arg_types = {"this": False}
2118
2119
2120class MergeBlockRatioProperty(Property):
2121    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2122
2123
2124class NoPrimaryIndexProperty(Property):
2125    arg_types = {}
2126
2127
2128class OnProperty(Property):
2129    arg_types = {"this": True}
2130
2131
2132class OnCommitProperty(Property):
2133    arg_types = {"delete": False}
2134
2135
2136class PartitionedByProperty(Property):
2137    arg_types = {"this": True}
2138
2139
2140class ReturnsProperty(Property):
2141    arg_types = {"this": True, "is_table": False, "table": False}
2142
2143
2144class RowFormatProperty(Property):
2145    arg_types = {"this": True}
2146
2147
2148class RowFormatDelimitedProperty(Property):
2149    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2150    arg_types = {
2151        "fields": False,
2152        "escaped": False,
2153        "collection_items": False,
2154        "map_keys": False,
2155        "lines": False,
2156        "null": False,
2157        "serde": False,
2158    }
2159
2160
2161class RowFormatSerdeProperty(Property):
2162    arg_types = {"this": True, "serde_properties": False}
2163
2164
2165# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2166class QueryTransform(Expression):
2167    arg_types = {
2168        "expressions": True,
2169        "command_script": True,
2170        "schema": False,
2171        "row_format_before": False,
2172        "record_writer": False,
2173        "row_format_after": False,
2174        "record_reader": False,
2175    }
2176
2177
2178class SampleProperty(Property):
2179    arg_types = {"this": True}
2180
2181
2182class SchemaCommentProperty(Property):
2183    arg_types = {"this": True}
2184
2185
2186class SerdeProperties(Property):
2187    arg_types = {"expressions": True}
2188
2189
2190class SetProperty(Property):
2191    arg_types = {"multi": True}
2192
2193
2194class SettingsProperty(Property):
2195    arg_types = {"expressions": True}
2196
2197
2198class SortKeyProperty(Property):
2199    arg_types = {"this": True, "compound": False}
2200
2201
2202class SqlSecurityProperty(Property):
2203    arg_types = {"definer": True}
2204
2205
2206class StabilityProperty(Property):
2207    arg_types = {"this": True}
2208
2209
2210class TemporaryProperty(Property):
2211    arg_types = {}
2212
2213
2214class TransientProperty(Property):
2215    arg_types = {"this": False}
2216
2217
2218class VolatileProperty(Property):
2219    arg_types = {"this": False}
2220
2221
2222class WithDataProperty(Property):
2223    arg_types = {"no": True, "statistics": False}
2224
2225
2226class WithJournalTableProperty(Property):
2227    arg_types = {"this": True}
2228
2229
2230class Properties(Expression):
2231    arg_types = {"expressions": True}
2232
2233    NAME_TO_PROPERTY = {
2234        "ALGORITHM": AlgorithmProperty,
2235        "AUTO_INCREMENT": AutoIncrementProperty,
2236        "CHARACTER SET": CharacterSetProperty,
2237        "CLUSTERED_BY": ClusteredByProperty,
2238        "COLLATE": CollateProperty,
2239        "COMMENT": SchemaCommentProperty,
2240        "DEFINER": DefinerProperty,
2241        "DISTKEY": DistKeyProperty,
2242        "DISTSTYLE": DistStyleProperty,
2243        "ENGINE": EngineProperty,
2244        "EXECUTE AS": ExecuteAsProperty,
2245        "FORMAT": FileFormatProperty,
2246        "LANGUAGE": LanguageProperty,
2247        "LOCATION": LocationProperty,
2248        "PARTITIONED_BY": PartitionedByProperty,
2249        "RETURNS": ReturnsProperty,
2250        "ROW_FORMAT": RowFormatProperty,
2251        "SORTKEY": SortKeyProperty,
2252    }
2253
2254    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2255
2256    # CREATE property locations
2257    # Form: schema specified
2258    #   create [POST_CREATE]
2259    #     table a [POST_NAME]
2260    #     (b int) [POST_SCHEMA]
2261    #     with ([POST_WITH])
2262    #     index (b) [POST_INDEX]
2263    #
2264    # Form: alias selection
2265    #   create [POST_CREATE]
2266    #     table a [POST_NAME]
2267    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2268    #     index (c) [POST_INDEX]
2269    class Location(AutoName):
2270        POST_CREATE = auto()
2271        POST_NAME = auto()
2272        POST_SCHEMA = auto()
2273        POST_WITH = auto()
2274        POST_ALIAS = auto()
2275        POST_EXPRESSION = auto()
2276        POST_INDEX = auto()
2277        UNSUPPORTED = auto()
2278
2279    @classmethod
2280    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2281        expressions = []
2282        for key, value in properties_dict.items():
2283            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2284            if property_cls:
2285                expressions.append(property_cls(this=convert(value)))
2286            else:
2287                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2288
2289        return cls(expressions=expressions)
2290
2291
2292class Qualify(Expression):
2293    pass
2294
2295
2296# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2297class Return(Expression):
2298    pass
2299
2300
2301class Reference(Expression):
2302    arg_types = {"this": True, "expressions": False, "options": False}
2303
2304
2305class Tuple(Expression):
2306    arg_types = {"expressions": False}
2307
2308    def isin(
2309        self,
2310        *expressions: t.Any,
2311        query: t.Optional[ExpOrStr] = None,
2312        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2313        copy: bool = True,
2314        **opts,
2315    ) -> In:
2316        return In(
2317            this=maybe_copy(self, copy),
2318            expressions=[convert(e, copy=copy) for e in expressions],
2319            query=maybe_parse(query, copy=copy, **opts) if query else None,
2320            unnest=Unnest(
2321                expressions=[
2322                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2323                ]
2324            )
2325            if unnest
2326            else None,
2327        )
2328
2329
2330class Subqueryable(Unionable):
2331    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2332        """
2333        Convert this expression to an aliased expression that can be used as a Subquery.
2334
2335        Example:
2336            >>> subquery = Select().select("x").from_("tbl").subquery()
2337            >>> Select().select("x").from_(subquery).sql()
2338            'SELECT x FROM (SELECT x FROM tbl)'
2339
2340        Args:
2341            alias (str | Identifier): an optional alias for the subquery
2342            copy (bool): if `False`, modify this expression instance in-place.
2343
2344        Returns:
2345            Alias: the subquery
2346        """
2347        instance = maybe_copy(self, copy)
2348        if not isinstance(alias, Expression):
2349            alias = TableAlias(this=to_identifier(alias)) if alias else None
2350
2351        return Subquery(this=instance, alias=alias)
2352
2353    def limit(
2354        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2355    ) -> Select:
2356        raise NotImplementedError
2357
2358    @property
2359    def ctes(self):
2360        with_ = self.args.get("with")
2361        if not with_:
2362            return []
2363        return with_.expressions
2364
2365    @property
2366    def selects(self) -> t.List[Expression]:
2367        raise NotImplementedError("Subqueryable objects must implement `selects`")
2368
2369    @property
2370    def named_selects(self) -> t.List[str]:
2371        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2372
2373    def select(
2374        self,
2375        *expressions: t.Optional[ExpOrStr],
2376        append: bool = True,
2377        dialect: DialectType = None,
2378        copy: bool = True,
2379        **opts,
2380    ) -> Subqueryable:
2381        raise NotImplementedError("Subqueryable objects must implement `select`")
2382
2383    def with_(
2384        self,
2385        alias: ExpOrStr,
2386        as_: ExpOrStr,
2387        recursive: t.Optional[bool] = None,
2388        append: bool = True,
2389        dialect: DialectType = None,
2390        copy: bool = True,
2391        **opts,
2392    ) -> Subqueryable:
2393        """
2394        Append to or set the common table expressions.
2395
2396        Example:
2397            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2398            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2399
2400        Args:
2401            alias: the SQL code string to parse as the table name.
2402                If an `Expression` instance is passed, this is used as-is.
2403            as_: the SQL code string to parse as the table expression.
2404                If an `Expression` instance is passed, it will be used as-is.
2405            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2406            append: if `True`, add to any existing expressions.
2407                Otherwise, this resets the expressions.
2408            dialect: the dialect used to parse the input expression.
2409            copy: if `False`, modify this expression instance in-place.
2410            opts: other options to use to parse the input expressions.
2411
2412        Returns:
2413            The modified expression.
2414        """
2415        return _apply_cte_builder(
2416            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2417        )
2418
2419
2420QUERY_MODIFIERS = {
2421    "match": False,
2422    "laterals": False,
2423    "joins": False,
2424    "connect": False,
2425    "pivots": False,
2426    "where": False,
2427    "group": False,
2428    "having": False,
2429    "qualify": False,
2430    "windows": False,
2431    "distribute": False,
2432    "sort": False,
2433    "cluster": False,
2434    "order": False,
2435    "limit": False,
2436    "offset": False,
2437    "locks": False,
2438    "sample": False,
2439    "settings": False,
2440    "format": False,
2441}
2442
2443
2444# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2445class WithTableHint(Expression):
2446    arg_types = {"expressions": True}
2447
2448
2449# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2450class IndexTableHint(Expression):
2451    arg_types = {"this": True, "expressions": False, "target": False}
2452
2453
2454class Table(Expression):
2455    arg_types = {
2456        "this": True,
2457        "alias": False,
2458        "db": False,
2459        "catalog": False,
2460        "laterals": False,
2461        "joins": False,
2462        "pivots": False,
2463        "hints": False,
2464        "system_time": False,
2465        "version": False,
2466        "format": False,
2467        "pattern": False,
2468        "index": False,
2469    }
2470
2471    @property
2472    def name(self) -> str:
2473        if isinstance(self.this, Func):
2474            return ""
2475        return self.this.name
2476
2477    @property
2478    def db(self) -> str:
2479        return self.text("db")
2480
2481    @property
2482    def catalog(self) -> str:
2483        return self.text("catalog")
2484
2485    @property
2486    def selects(self) -> t.List[Expression]:
2487        return []
2488
2489    @property
2490    def named_selects(self) -> t.List[str]:
2491        return []
2492
2493    @property
2494    def parts(self) -> t.List[Expression]:
2495        """Return the parts of a table in order catalog, db, table."""
2496        parts: t.List[Expression] = []
2497
2498        for arg in ("catalog", "db", "this"):
2499            part = self.args.get(arg)
2500
2501            if isinstance(part, Dot):
2502                parts.extend(part.flatten())
2503            elif isinstance(part, Expression):
2504                parts.append(part)
2505
2506        return parts
2507
2508
2509class Union(Subqueryable):
2510    arg_types = {
2511        "with": False,
2512        "this": True,
2513        "expression": True,
2514        "distinct": False,
2515        "by_name": False,
2516        **QUERY_MODIFIERS,
2517    }
2518
2519    def limit(
2520        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2521    ) -> Select:
2522        """
2523        Set the LIMIT expression.
2524
2525        Example:
2526            >>> select("1").union(select("1")).limit(1).sql()
2527            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2528
2529        Args:
2530            expression: the SQL code string to parse.
2531                This can also be an integer.
2532                If a `Limit` instance is passed, this is used as-is.
2533                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2534            dialect: the dialect used to parse the input expression.
2535            copy: if `False`, modify this expression instance in-place.
2536            opts: other options to use to parse the input expressions.
2537
2538        Returns:
2539            The limited subqueryable.
2540        """
2541        return (
2542            select("*")
2543            .from_(self.subquery(alias="_l_0", copy=copy))
2544            .limit(expression, dialect=dialect, copy=False, **opts)
2545        )
2546
2547    def select(
2548        self,
2549        *expressions: t.Optional[ExpOrStr],
2550        append: bool = True,
2551        dialect: DialectType = None,
2552        copy: bool = True,
2553        **opts,
2554    ) -> Union:
2555        """Append to or set the SELECT of the union recursively.
2556
2557        Example:
2558            >>> from sqlglot import parse_one
2559            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2560            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2561
2562        Args:
2563            *expressions: the SQL code strings to parse.
2564                If an `Expression` instance is passed, it will be used as-is.
2565            append: if `True`, add to any existing expressions.
2566                Otherwise, this resets the expressions.
2567            dialect: the dialect used to parse the input expressions.
2568            copy: if `False`, modify this expression instance in-place.
2569            opts: other options to use to parse the input expressions.
2570
2571        Returns:
2572            Union: the modified expression.
2573        """
2574        this = self.copy() if copy else self
2575        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2576        this.expression.unnest().select(
2577            *expressions, append=append, dialect=dialect, copy=False, **opts
2578        )
2579        return this
2580
2581    @property
2582    def named_selects(self) -> t.List[str]:
2583        return self.this.unnest().named_selects
2584
2585    @property
2586    def is_star(self) -> bool:
2587        return self.this.is_star or self.expression.is_star
2588
2589    @property
2590    def selects(self) -> t.List[Expression]:
2591        return self.this.unnest().selects
2592
2593    @property
2594    def left(self):
2595        return self.this
2596
2597    @property
2598    def right(self):
2599        return self.expression
2600
2601
2602class Except(Union):
2603    pass
2604
2605
2606class Intersect(Union):
2607    pass
2608
2609
2610class Unnest(UDTF):
2611    arg_types = {
2612        "expressions": True,
2613        "alias": False,
2614        "offset": False,
2615    }
2616
2617
2618class Update(Expression):
2619    arg_types = {
2620        "with": False,
2621        "this": False,
2622        "expressions": True,
2623        "from": False,
2624        "where": False,
2625        "returning": False,
2626        "order": False,
2627        "limit": False,
2628    }
2629
2630
2631class Values(UDTF):
2632    arg_types = {
2633        "expressions": True,
2634        "ordinality": False,
2635        "alias": False,
2636    }
2637
2638
2639class Var(Expression):
2640    pass
2641
2642
2643class Version(Expression):
2644    """
2645    Time travel, iceberg, bigquery etc
2646    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2647    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2648    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2649    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2650    this is either TIMESTAMP or VERSION
2651    kind is ("AS OF", "BETWEEN")
2652    """
2653
2654    arg_types = {"this": True, "kind": True, "expression": False}
2655
2656
2657class Schema(Expression):
2658    arg_types = {"this": False, "expressions": False}
2659
2660
2661# https://dev.mysql.com/doc/refman/8.0/en/select.html
2662# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2663class Lock(Expression):
2664    arg_types = {"update": True, "expressions": False, "wait": False}
2665
2666
2667class Select(Subqueryable):
2668    arg_types = {
2669        "with": False,
2670        "kind": False,
2671        "expressions": False,
2672        "hint": False,
2673        "distinct": False,
2674        "into": False,
2675        "from": False,
2676        **QUERY_MODIFIERS,
2677    }
2678
2679    def from_(
2680        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2681    ) -> Select:
2682        """
2683        Set the FROM expression.
2684
2685        Example:
2686            >>> Select().from_("tbl").select("x").sql()
2687            'SELECT x FROM tbl'
2688
2689        Args:
2690            expression : the SQL code strings to parse.
2691                If a `From` instance is passed, this is used as-is.
2692                If another `Expression` instance is passed, it will be wrapped in a `From`.
2693            dialect: the dialect used to parse the input expression.
2694            copy: if `False`, modify this expression instance in-place.
2695            opts: other options to use to parse the input expressions.
2696
2697        Returns:
2698            The modified Select expression.
2699        """
2700        return _apply_builder(
2701            expression=expression,
2702            instance=self,
2703            arg="from",
2704            into=From,
2705            prefix="FROM",
2706            dialect=dialect,
2707            copy=copy,
2708            **opts,
2709        )
2710
2711    def group_by(
2712        self,
2713        *expressions: t.Optional[ExpOrStr],
2714        append: bool = True,
2715        dialect: DialectType = None,
2716        copy: bool = True,
2717        **opts,
2718    ) -> Select:
2719        """
2720        Set the GROUP BY expression.
2721
2722        Example:
2723            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2724            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2725
2726        Args:
2727            *expressions: the SQL code strings to parse.
2728                If a `Group` instance is passed, this is used as-is.
2729                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2730                If nothing is passed in then a group by is not applied to the expression
2731            append: if `True`, add to any existing expressions.
2732                Otherwise, this flattens all the `Group` expression into a single expression.
2733            dialect: the dialect used to parse the input expression.
2734            copy: if `False`, modify this expression instance in-place.
2735            opts: other options to use to parse the input expressions.
2736
2737        Returns:
2738            The modified Select expression.
2739        """
2740        if not expressions:
2741            return self if not copy else self.copy()
2742
2743        return _apply_child_list_builder(
2744            *expressions,
2745            instance=self,
2746            arg="group",
2747            append=append,
2748            copy=copy,
2749            prefix="GROUP BY",
2750            into=Group,
2751            dialect=dialect,
2752            **opts,
2753        )
2754
2755    def order_by(
2756        self,
2757        *expressions: t.Optional[ExpOrStr],
2758        append: bool = True,
2759        dialect: DialectType = None,
2760        copy: bool = True,
2761        **opts,
2762    ) -> Select:
2763        """
2764        Set the ORDER BY expression.
2765
2766        Example:
2767            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2768            'SELECT x FROM tbl ORDER BY x DESC'
2769
2770        Args:
2771            *expressions: the SQL code strings to parse.
2772                If a `Group` instance is passed, this is used as-is.
2773                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2774            append: if `True`, add to any existing expressions.
2775                Otherwise, this flattens all the `Order` expression into a single expression.
2776            dialect: the dialect used to parse the input expression.
2777            copy: if `False`, modify this expression instance in-place.
2778            opts: other options to use to parse the input expressions.
2779
2780        Returns:
2781            The modified Select expression.
2782        """
2783        return _apply_child_list_builder(
2784            *expressions,
2785            instance=self,
2786            arg="order",
2787            append=append,
2788            copy=copy,
2789            prefix="ORDER BY",
2790            into=Order,
2791            dialect=dialect,
2792            **opts,
2793        )
2794
2795    def sort_by(
2796        self,
2797        *expressions: t.Optional[ExpOrStr],
2798        append: bool = True,
2799        dialect: DialectType = None,
2800        copy: bool = True,
2801        **opts,
2802    ) -> Select:
2803        """
2804        Set the SORT BY expression.
2805
2806        Example:
2807            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2808            'SELECT x FROM tbl SORT BY x DESC'
2809
2810        Args:
2811            *expressions: the SQL code strings to parse.
2812                If a `Group` instance is passed, this is used as-is.
2813                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2814            append: if `True`, add to any existing expressions.
2815                Otherwise, this flattens all the `Order` expression into a single expression.
2816            dialect: the dialect used to parse the input expression.
2817            copy: if `False`, modify this expression instance in-place.
2818            opts: other options to use to parse the input expressions.
2819
2820        Returns:
2821            The modified Select expression.
2822        """
2823        return _apply_child_list_builder(
2824            *expressions,
2825            instance=self,
2826            arg="sort",
2827            append=append,
2828            copy=copy,
2829            prefix="SORT BY",
2830            into=Sort,
2831            dialect=dialect,
2832            **opts,
2833        )
2834
2835    def cluster_by(
2836        self,
2837        *expressions: t.Optional[ExpOrStr],
2838        append: bool = True,
2839        dialect: DialectType = None,
2840        copy: bool = True,
2841        **opts,
2842    ) -> Select:
2843        """
2844        Set the CLUSTER BY expression.
2845
2846        Example:
2847            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2848            'SELECT x FROM tbl CLUSTER BY x DESC'
2849
2850        Args:
2851            *expressions: the SQL code strings to parse.
2852                If a `Group` instance is passed, this is used as-is.
2853                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2854            append: if `True`, add to any existing expressions.
2855                Otherwise, this flattens all the `Order` expression into a single expression.
2856            dialect: the dialect used to parse the input expression.
2857            copy: if `False`, modify this expression instance in-place.
2858            opts: other options to use to parse the input expressions.
2859
2860        Returns:
2861            The modified Select expression.
2862        """
2863        return _apply_child_list_builder(
2864            *expressions,
2865            instance=self,
2866            arg="cluster",
2867            append=append,
2868            copy=copy,
2869            prefix="CLUSTER BY",
2870            into=Cluster,
2871            dialect=dialect,
2872            **opts,
2873        )
2874
2875    def limit(
2876        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2877    ) -> Select:
2878        """
2879        Set the LIMIT expression.
2880
2881        Example:
2882            >>> Select().from_("tbl").select("x").limit(10).sql()
2883            'SELECT x FROM tbl LIMIT 10'
2884
2885        Args:
2886            expression: the SQL code string to parse.
2887                This can also be an integer.
2888                If a `Limit` instance is passed, this is used as-is.
2889                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2890            dialect: the dialect used to parse the input expression.
2891            copy: if `False`, modify this expression instance in-place.
2892            opts: other options to use to parse the input expressions.
2893
2894        Returns:
2895            Select: the modified expression.
2896        """
2897        return _apply_builder(
2898            expression=expression,
2899            instance=self,
2900            arg="limit",
2901            into=Limit,
2902            prefix="LIMIT",
2903            dialect=dialect,
2904            copy=copy,
2905            into_arg="expression",
2906            **opts,
2907        )
2908
2909    def offset(
2910        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2911    ) -> Select:
2912        """
2913        Set the OFFSET expression.
2914
2915        Example:
2916            >>> Select().from_("tbl").select("x").offset(10).sql()
2917            'SELECT x FROM tbl OFFSET 10'
2918
2919        Args:
2920            expression: the SQL code string to parse.
2921                This can also be an integer.
2922                If a `Offset` instance is passed, this is used as-is.
2923                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2924            dialect: the dialect used to parse the input expression.
2925            copy: if `False`, modify this expression instance in-place.
2926            opts: other options to use to parse the input expressions.
2927
2928        Returns:
2929            The modified Select expression.
2930        """
2931        return _apply_builder(
2932            expression=expression,
2933            instance=self,
2934            arg="offset",
2935            into=Offset,
2936            prefix="OFFSET",
2937            dialect=dialect,
2938            copy=copy,
2939            into_arg="expression",
2940            **opts,
2941        )
2942
2943    def select(
2944        self,
2945        *expressions: t.Optional[ExpOrStr],
2946        append: bool = True,
2947        dialect: DialectType = None,
2948        copy: bool = True,
2949        **opts,
2950    ) -> Select:
2951        """
2952        Append to or set the SELECT expressions.
2953
2954        Example:
2955            >>> Select().select("x", "y").sql()
2956            'SELECT x, y'
2957
2958        Args:
2959            *expressions: the SQL code strings to parse.
2960                If an `Expression` instance is passed, it will be used as-is.
2961            append: if `True`, add to any existing expressions.
2962                Otherwise, this resets the expressions.
2963            dialect: the dialect used to parse the input expressions.
2964            copy: if `False`, modify this expression instance in-place.
2965            opts: other options to use to parse the input expressions.
2966
2967        Returns:
2968            The modified Select expression.
2969        """
2970        return _apply_list_builder(
2971            *expressions,
2972            instance=self,
2973            arg="expressions",
2974            append=append,
2975            dialect=dialect,
2976            copy=copy,
2977            **opts,
2978        )
2979
2980    def lateral(
2981        self,
2982        *expressions: t.Optional[ExpOrStr],
2983        append: bool = True,
2984        dialect: DialectType = None,
2985        copy: bool = True,
2986        **opts,
2987    ) -> Select:
2988        """
2989        Append to or set the LATERAL expressions.
2990
2991        Example:
2992            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2993            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2994
2995        Args:
2996            *expressions: the SQL code strings to parse.
2997                If an `Expression` instance is passed, it will be used as-is.
2998            append: if `True`, add to any existing expressions.
2999                Otherwise, this resets the expressions.
3000            dialect: the dialect used to parse the input expressions.
3001            copy: if `False`, modify this expression instance in-place.
3002            opts: other options to use to parse the input expressions.
3003
3004        Returns:
3005            The modified Select expression.
3006        """
3007        return _apply_list_builder(
3008            *expressions,
3009            instance=self,
3010            arg="laterals",
3011            append=append,
3012            into=Lateral,
3013            prefix="LATERAL VIEW",
3014            dialect=dialect,
3015            copy=copy,
3016            **opts,
3017        )
3018
3019    def join(
3020        self,
3021        expression: ExpOrStr,
3022        on: t.Optional[ExpOrStr] = None,
3023        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3024        append: bool = True,
3025        join_type: t.Optional[str] = None,
3026        join_alias: t.Optional[Identifier | str] = None,
3027        dialect: DialectType = None,
3028        copy: bool = True,
3029        **opts,
3030    ) -> Select:
3031        """
3032        Append to or set the JOIN expressions.
3033
3034        Example:
3035            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3036            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3037
3038            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3039            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3040
3041            Use `join_type` to change the type of join:
3042
3043            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3044            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3045
3046        Args:
3047            expression: the SQL code string to parse.
3048                If an `Expression` instance is passed, it will be used as-is.
3049            on: optionally specify the join "on" criteria as a SQL string.
3050                If an `Expression` instance is passed, it will be used as-is.
3051            using: optionally specify the join "using" criteria as a SQL string.
3052                If an `Expression` instance is passed, it will be used as-is.
3053            append: if `True`, add to any existing expressions.
3054                Otherwise, this resets the expressions.
3055            join_type: if set, alter the parsed join type.
3056            join_alias: an optional alias for the joined source.
3057            dialect: the dialect used to parse the input expressions.
3058            copy: if `False`, modify this expression instance in-place.
3059            opts: other options to use to parse the input expressions.
3060
3061        Returns:
3062            Select: the modified expression.
3063        """
3064        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3065
3066        try:
3067            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3068        except ParseError:
3069            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3070
3071        join = expression if isinstance(expression, Join) else Join(this=expression)
3072
3073        if isinstance(join.this, Select):
3074            join.this.replace(join.this.subquery())
3075
3076        if join_type:
3077            method: t.Optional[Token]
3078            side: t.Optional[Token]
3079            kind: t.Optional[Token]
3080
3081            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3082
3083            if method:
3084                join.set("method", method.text)
3085            if side:
3086                join.set("side", side.text)
3087            if kind:
3088                join.set("kind", kind.text)
3089
3090        if on:
3091            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3092            join.set("on", on)
3093
3094        if using:
3095            join = _apply_list_builder(
3096                *ensure_list(using),
3097                instance=join,
3098                arg="using",
3099                append=append,
3100                copy=copy,
3101                into=Identifier,
3102                **opts,
3103            )
3104
3105        if join_alias:
3106            join.set("this", alias_(join.this, join_alias, table=True))
3107
3108        return _apply_list_builder(
3109            join,
3110            instance=self,
3111            arg="joins",
3112            append=append,
3113            copy=copy,
3114            **opts,
3115        )
3116
3117    def where(
3118        self,
3119        *expressions: t.Optional[ExpOrStr],
3120        append: bool = True,
3121        dialect: DialectType = None,
3122        copy: bool = True,
3123        **opts,
3124    ) -> Select:
3125        """
3126        Append to or set the WHERE expressions.
3127
3128        Example:
3129            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3130            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3131
3132        Args:
3133            *expressions: the SQL code strings to parse.
3134                If an `Expression` instance is passed, it will be used as-is.
3135                Multiple expressions are combined with an AND operator.
3136            append: if `True`, AND the new expressions to any existing expression.
3137                Otherwise, this resets the expression.
3138            dialect: the dialect used to parse the input expressions.
3139            copy: if `False`, modify this expression instance in-place.
3140            opts: other options to use to parse the input expressions.
3141
3142        Returns:
3143            Select: the modified expression.
3144        """
3145        return _apply_conjunction_builder(
3146            *expressions,
3147            instance=self,
3148            arg="where",
3149            append=append,
3150            into=Where,
3151            dialect=dialect,
3152            copy=copy,
3153            **opts,
3154        )
3155
3156    def having(
3157        self,
3158        *expressions: t.Optional[ExpOrStr],
3159        append: bool = True,
3160        dialect: DialectType = None,
3161        copy: bool = True,
3162        **opts,
3163    ) -> Select:
3164        """
3165        Append to or set the HAVING expressions.
3166
3167        Example:
3168            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3169            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3170
3171        Args:
3172            *expressions: the SQL code strings to parse.
3173                If an `Expression` instance is passed, it will be used as-is.
3174                Multiple expressions are combined with an AND operator.
3175            append: if `True`, AND the new expressions to any existing expression.
3176                Otherwise, this resets the expression.
3177            dialect: the dialect used to parse the input expressions.
3178            copy: if `False`, modify this expression instance in-place.
3179            opts: other options to use to parse the input expressions.
3180
3181        Returns:
3182            The modified Select expression.
3183        """
3184        return _apply_conjunction_builder(
3185            *expressions,
3186            instance=self,
3187            arg="having",
3188            append=append,
3189            into=Having,
3190            dialect=dialect,
3191            copy=copy,
3192            **opts,
3193        )
3194
3195    def window(
3196        self,
3197        *expressions: t.Optional[ExpOrStr],
3198        append: bool = True,
3199        dialect: DialectType = None,
3200        copy: bool = True,
3201        **opts,
3202    ) -> Select:
3203        return _apply_list_builder(
3204            *expressions,
3205            instance=self,
3206            arg="windows",
3207            append=append,
3208            into=Window,
3209            dialect=dialect,
3210            copy=copy,
3211            **opts,
3212        )
3213
3214    def qualify(
3215        self,
3216        *expressions: t.Optional[ExpOrStr],
3217        append: bool = True,
3218        dialect: DialectType = None,
3219        copy: bool = True,
3220        **opts,
3221    ) -> Select:
3222        return _apply_conjunction_builder(
3223            *expressions,
3224            instance=self,
3225            arg="qualify",
3226            append=append,
3227            into=Qualify,
3228            dialect=dialect,
3229            copy=copy,
3230            **opts,
3231        )
3232
3233    def distinct(
3234        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3235    ) -> Select:
3236        """
3237        Set the OFFSET expression.
3238
3239        Example:
3240            >>> Select().from_("tbl").select("x").distinct().sql()
3241            'SELECT DISTINCT x FROM tbl'
3242
3243        Args:
3244            ons: the expressions to distinct on
3245            distinct: whether the Select should be distinct
3246            copy: if `False`, modify this expression instance in-place.
3247
3248        Returns:
3249            Select: the modified expression.
3250        """
3251        instance = maybe_copy(self, copy)
3252        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3253        instance.set("distinct", Distinct(on=on) if distinct else None)
3254        return instance
3255
3256    def ctas(
3257        self,
3258        table: ExpOrStr,
3259        properties: t.Optional[t.Dict] = None,
3260        dialect: DialectType = None,
3261        copy: bool = True,
3262        **opts,
3263    ) -> Create:
3264        """
3265        Convert this expression to a CREATE TABLE AS statement.
3266
3267        Example:
3268            >>> Select().select("*").from_("tbl").ctas("x").sql()
3269            'CREATE TABLE x AS SELECT * FROM tbl'
3270
3271        Args:
3272            table: the SQL code string to parse as the table name.
3273                If another `Expression` instance is passed, it will be used as-is.
3274            properties: an optional mapping of table properties
3275            dialect: the dialect used to parse the input table.
3276            copy: if `False`, modify this expression instance in-place.
3277            opts: other options to use to parse the input table.
3278
3279        Returns:
3280            The new Create expression.
3281        """
3282        instance = maybe_copy(self, copy)
3283        table_expression = maybe_parse(
3284            table,
3285            into=Table,
3286            dialect=dialect,
3287            **opts,
3288        )
3289        properties_expression = None
3290        if properties:
3291            properties_expression = Properties.from_dict(properties)
3292
3293        return Create(
3294            this=table_expression,
3295            kind="table",
3296            expression=instance,
3297            properties=properties_expression,
3298        )
3299
3300    def lock(self, update: bool = True, copy: bool = True) -> Select:
3301        """
3302        Set the locking read mode for this expression.
3303
3304        Examples:
3305            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3306            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3307
3308            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3309            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3310
3311        Args:
3312            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3313            copy: if `False`, modify this expression instance in-place.
3314
3315        Returns:
3316            The modified expression.
3317        """
3318        inst = maybe_copy(self, copy)
3319        inst.set("locks", [Lock(update=update)])
3320
3321        return inst
3322
3323    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3324        """
3325        Set hints for this expression.
3326
3327        Examples:
3328            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3329            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3330
3331        Args:
3332            hints: The SQL code strings to parse as the hints.
3333                If an `Expression` instance is passed, it will be used as-is.
3334            dialect: The dialect used to parse the hints.
3335            copy: If `False`, modify this expression instance in-place.
3336
3337        Returns:
3338            The modified expression.
3339        """
3340        inst = maybe_copy(self, copy)
3341        inst.set(
3342            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3343        )
3344
3345        return inst
3346
3347    @property
3348    def named_selects(self) -> t.List[str]:
3349        return [e.output_name for e in self.expressions if e.alias_or_name]
3350
3351    @property
3352    def is_star(self) -> bool:
3353        return any(expression.is_star for expression in self.expressions)
3354
3355    @property
3356    def selects(self) -> t.List[Expression]:
3357        return self.expressions
3358
3359
3360class Subquery(DerivedTable, Unionable):
3361    arg_types = {
3362        "this": True,
3363        "alias": False,
3364        "with": False,
3365        **QUERY_MODIFIERS,
3366    }
3367
3368    def unnest(self):
3369        """
3370        Returns the first non subquery.
3371        """
3372        expression = self
3373        while isinstance(expression, Subquery):
3374            expression = expression.this
3375        return expression
3376
3377    def unwrap(self) -> Subquery:
3378        expression = self
3379        while expression.same_parent and expression.is_wrapper:
3380            expression = t.cast(Subquery, expression.parent)
3381        return expression
3382
3383    @property
3384    def is_wrapper(self) -> bool:
3385        """
3386        Whether this Subquery acts as a simple wrapper around another expression.
3387
3388        SELECT * FROM (((SELECT * FROM t)))
3389                      ^
3390                      This corresponds to a "wrapper" Subquery node
3391        """
3392        return all(v is None for k, v in self.args.items() if k != "this")
3393
3394    @property
3395    def is_star(self) -> bool:
3396        return self.this.is_star
3397
3398    @property
3399    def output_name(self) -> str:
3400        return self.alias
3401
3402
3403class TableSample(Expression):
3404    arg_types = {
3405        "this": False,
3406        "expressions": False,
3407        "method": False,
3408        "bucket_numerator": False,
3409        "bucket_denominator": False,
3410        "bucket_field": False,
3411        "percent": False,
3412        "rows": False,
3413        "size": False,
3414        "seed": False,
3415        "kind": False,
3416    }
3417
3418
3419class Tag(Expression):
3420    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3421
3422    arg_types = {
3423        "this": False,
3424        "prefix": False,
3425        "postfix": False,
3426    }
3427
3428
3429# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3430# https://duckdb.org/docs/sql/statements/pivot
3431class Pivot(Expression):
3432    arg_types = {
3433        "this": False,
3434        "alias": False,
3435        "expressions": False,
3436        "field": False,
3437        "unpivot": False,
3438        "using": False,
3439        "group": False,
3440        "columns": False,
3441        "include_nulls": False,
3442    }
3443
3444
3445class Window(Condition):
3446    arg_types = {
3447        "this": True,
3448        "partition_by": False,
3449        "order": False,
3450        "spec": False,
3451        "alias": False,
3452        "over": False,
3453        "first": False,
3454    }
3455
3456
3457class WindowSpec(Expression):
3458    arg_types = {
3459        "kind": False,
3460        "start": False,
3461        "start_side": False,
3462        "end": False,
3463        "end_side": False,
3464    }
3465
3466
3467class Where(Expression):
3468    pass
3469
3470
3471class Star(Expression):
3472    arg_types = {"except": False, "replace": False}
3473
3474    @property
3475    def name(self) -> str:
3476        return "*"
3477
3478    @property
3479    def output_name(self) -> str:
3480        return self.name
3481
3482
3483class Parameter(Condition):
3484    arg_types = {"this": True, "wrapped": False}
3485
3486
3487class SessionParameter(Condition):
3488    arg_types = {"this": True, "kind": False}
3489
3490
3491class Placeholder(Condition):
3492    arg_types = {"this": False, "kind": False}
3493
3494
3495class Null(Condition):
3496    arg_types: t.Dict[str, t.Any] = {}
3497
3498    @property
3499    def name(self) -> str:
3500        return "NULL"
3501
3502
3503class Boolean(Condition):
3504    pass
3505
3506
3507class DataTypeParam(Expression):
3508    arg_types = {"this": True, "expression": False}
3509
3510
3511class DataType(Expression):
3512    arg_types = {
3513        "this": True,
3514        "expressions": False,
3515        "nested": False,
3516        "values": False,
3517        "prefix": False,
3518        "kind": False,
3519    }
3520
3521    class Type(AutoName):
3522        ARRAY = auto()
3523        BIGDECIMAL = auto()
3524        BIGINT = auto()
3525        BIGSERIAL = auto()
3526        BINARY = auto()
3527        BIT = auto()
3528        BOOLEAN = auto()
3529        CHAR = auto()
3530        DATE = auto()
3531        DATEMULTIRANGE = auto()
3532        DATERANGE = auto()
3533        DATETIME = auto()
3534        DATETIME64 = auto()
3535        DECIMAL = auto()
3536        DOUBLE = auto()
3537        ENUM = auto()
3538        ENUM8 = auto()
3539        ENUM16 = auto()
3540        FIXEDSTRING = auto()
3541        FLOAT = auto()
3542        GEOGRAPHY = auto()
3543        GEOMETRY = auto()
3544        HLLSKETCH = auto()
3545        HSTORE = auto()
3546        IMAGE = auto()
3547        INET = auto()
3548        INT = auto()
3549        INT128 = auto()
3550        INT256 = auto()
3551        INT4MULTIRANGE = auto()
3552        INT4RANGE = auto()
3553        INT8MULTIRANGE = auto()
3554        INT8RANGE = auto()
3555        INTERVAL = auto()
3556        IPADDRESS = auto()
3557        IPPREFIX = auto()
3558        JSON = auto()
3559        JSONB = auto()
3560        LONGBLOB = auto()
3561        LONGTEXT = auto()
3562        LOWCARDINALITY = auto()
3563        MAP = auto()
3564        MEDIUMBLOB = auto()
3565        MEDIUMINT = auto()
3566        MEDIUMTEXT = auto()
3567        MONEY = auto()
3568        NCHAR = auto()
3569        NESTED = auto()
3570        NULL = auto()
3571        NULLABLE = auto()
3572        NUMMULTIRANGE = auto()
3573        NUMRANGE = auto()
3574        NVARCHAR = auto()
3575        OBJECT = auto()
3576        ROWVERSION = auto()
3577        SERIAL = auto()
3578        SET = auto()
3579        SMALLINT = auto()
3580        SMALLMONEY = auto()
3581        SMALLSERIAL = auto()
3582        STRUCT = auto()
3583        SUPER = auto()
3584        TEXT = auto()
3585        TINYBLOB = auto()
3586        TINYTEXT = auto()
3587        TIME = auto()
3588        TIMETZ = auto()
3589        TIMESTAMP = auto()
3590        TIMESTAMPLTZ = auto()
3591        TIMESTAMPTZ = auto()
3592        TINYINT = auto()
3593        TSMULTIRANGE = auto()
3594        TSRANGE = auto()
3595        TSTZMULTIRANGE = auto()
3596        TSTZRANGE = auto()
3597        UBIGINT = auto()
3598        UINT = auto()
3599        UINT128 = auto()
3600        UINT256 = auto()
3601        UMEDIUMINT = auto()
3602        UDECIMAL = auto()
3603        UNIQUEIDENTIFIER = auto()
3604        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3605        USERDEFINED = "USER-DEFINED"
3606        USMALLINT = auto()
3607        UTINYINT = auto()
3608        UUID = auto()
3609        VARBINARY = auto()
3610        VARCHAR = auto()
3611        VARIANT = auto()
3612        XML = auto()
3613        YEAR = auto()
3614
3615    TEXT_TYPES = {
3616        Type.CHAR,
3617        Type.NCHAR,
3618        Type.VARCHAR,
3619        Type.NVARCHAR,
3620        Type.TEXT,
3621    }
3622
3623    INTEGER_TYPES = {
3624        Type.INT,
3625        Type.TINYINT,
3626        Type.SMALLINT,
3627        Type.BIGINT,
3628        Type.INT128,
3629        Type.INT256,
3630    }
3631
3632    FLOAT_TYPES = {
3633        Type.FLOAT,
3634        Type.DOUBLE,
3635    }
3636
3637    NUMERIC_TYPES = {
3638        *INTEGER_TYPES,
3639        *FLOAT_TYPES,
3640    }
3641
3642    TEMPORAL_TYPES = {
3643        Type.TIME,
3644        Type.TIMETZ,
3645        Type.TIMESTAMP,
3646        Type.TIMESTAMPTZ,
3647        Type.TIMESTAMPLTZ,
3648        Type.DATE,
3649        Type.DATETIME,
3650        Type.DATETIME64,
3651    }
3652
3653    @classmethod
3654    def build(
3655        cls,
3656        dtype: str | DataType | DataType.Type,
3657        dialect: DialectType = None,
3658        udt: bool = False,
3659        **kwargs,
3660    ) -> DataType:
3661        """
3662        Constructs a DataType object.
3663
3664        Args:
3665            dtype: the data type of interest.
3666            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3667            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3668                DataType, thus creating a user-defined type.
3669            kawrgs: additional arguments to pass in the constructor of DataType.
3670
3671        Returns:
3672            The constructed DataType object.
3673        """
3674        from sqlglot import parse_one
3675
3676        if isinstance(dtype, str):
3677            if dtype.upper() == "UNKNOWN":
3678                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3679
3680            try:
3681                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3682            except ParseError:
3683                if udt:
3684                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3685                raise
3686        elif isinstance(dtype, DataType.Type):
3687            data_type_exp = DataType(this=dtype)
3688        elif isinstance(dtype, DataType):
3689            return dtype
3690        else:
3691            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3692
3693        return DataType(**{**data_type_exp.args, **kwargs})
3694
3695    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3696        """
3697        Checks whether this DataType matches one of the provided data types. Nested types or precision
3698        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3699
3700        Args:
3701            dtypes: the data types to compare this DataType to.
3702
3703        Returns:
3704            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3705        """
3706        for dtype in dtypes:
3707            other = DataType.build(dtype, udt=True)
3708
3709            if (
3710                other.expressions
3711                or self.this == DataType.Type.USERDEFINED
3712                or other.this == DataType.Type.USERDEFINED
3713            ):
3714                matches = self == other
3715            else:
3716                matches = self.this == other.this
3717
3718            if matches:
3719                return True
3720        return False
3721
3722
3723# https://www.postgresql.org/docs/15/datatype-pseudo.html
3724class PseudoType(DataType):
3725    arg_types = {"this": True}
3726
3727
3728# https://www.postgresql.org/docs/15/datatype-oid.html
3729class ObjectIdentifier(DataType):
3730    arg_types = {"this": True}
3731
3732
3733# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3734class SubqueryPredicate(Predicate):
3735    pass
3736
3737
3738class All(SubqueryPredicate):
3739    pass
3740
3741
3742class Any(SubqueryPredicate):
3743    pass
3744
3745
3746class Exists(SubqueryPredicate):
3747    pass
3748
3749
3750# Commands to interact with the databases or engines. For most of the command
3751# expressions we parse whatever comes after the command's name as a string.
3752class Command(Expression):
3753    arg_types = {"this": True, "expression": False}
3754
3755
3756class Transaction(Expression):
3757    arg_types = {"this": False, "modes": False, "mark": False}
3758
3759
3760class Commit(Expression):
3761    arg_types = {"chain": False, "this": False, "durability": False}
3762
3763
3764class Rollback(Expression):
3765    arg_types = {"savepoint": False, "this": False}
3766
3767
3768class AlterTable(Expression):
3769    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3770
3771
3772class AddConstraint(Expression):
3773    arg_types = {"this": False, "expression": False, "enforced": False}
3774
3775
3776class DropPartition(Expression):
3777    arg_types = {"expressions": True, "exists": False}
3778
3779
3780# Binary expressions like (ADD a b)
3781class Binary(Condition):
3782    arg_types = {"this": True, "expression": True}
3783
3784    @property
3785    def left(self):
3786        return self.this
3787
3788    @property
3789    def right(self):
3790        return self.expression
3791
3792
3793class Add(Binary):
3794    pass
3795
3796
3797class Connector(Binary):
3798    pass
3799
3800
3801class And(Connector):
3802    pass
3803
3804
3805class Or(Connector):
3806    pass
3807
3808
3809class BitwiseAnd(Binary):
3810    pass
3811
3812
3813class BitwiseLeftShift(Binary):
3814    pass
3815
3816
3817class BitwiseOr(Binary):
3818    pass
3819
3820
3821class BitwiseRightShift(Binary):
3822    pass
3823
3824
3825class BitwiseXor(Binary):
3826    pass
3827
3828
3829class Div(Binary):
3830    pass
3831
3832
3833class Overlaps(Binary):
3834    pass
3835
3836
3837class Dot(Binary):
3838    @property
3839    def name(self) -> str:
3840        return self.expression.name
3841
3842    @property
3843    def output_name(self) -> str:
3844        return self.name
3845
3846    @classmethod
3847    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3848        """Build a Dot object with a sequence of expressions."""
3849        if len(expressions) < 2:
3850            raise ValueError(f"Dot requires >= 2 expressions.")
3851
3852        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3853
3854
3855class DPipe(Binary):
3856    pass
3857
3858
3859class SafeDPipe(DPipe):
3860    pass
3861
3862
3863class EQ(Binary, Predicate):
3864    pass
3865
3866
3867class NullSafeEQ(Binary, Predicate):
3868    pass
3869
3870
3871class NullSafeNEQ(Binary, Predicate):
3872    pass
3873
3874
3875class Distance(Binary):
3876    pass
3877
3878
3879class Escape(Binary):
3880    pass
3881
3882
3883class Glob(Binary, Predicate):
3884    pass
3885
3886
3887class GT(Binary, Predicate):
3888    pass
3889
3890
3891class GTE(Binary, Predicate):
3892    pass
3893
3894
3895class ILike(Binary, Predicate):
3896    pass
3897
3898
3899class ILikeAny(Binary, Predicate):
3900    pass
3901
3902
3903class IntDiv(Binary):
3904    pass
3905
3906
3907class Is(Binary, Predicate):
3908    pass
3909
3910
3911class Kwarg(Binary):
3912    """Kwarg in special functions like func(kwarg => y)."""
3913
3914
3915class Like(Binary, Predicate):
3916    pass
3917
3918
3919class LikeAny(Binary, Predicate):
3920    pass
3921
3922
3923class LT(Binary, Predicate):
3924    pass
3925
3926
3927class LTE(Binary, Predicate):
3928    pass
3929
3930
3931class Mod(Binary):
3932    pass
3933
3934
3935class Mul(Binary):
3936    pass
3937
3938
3939class NEQ(Binary, Predicate):
3940    pass
3941
3942
3943class SimilarTo(Binary, Predicate):
3944    pass
3945
3946
3947class Slice(Binary):
3948    arg_types = {"this": False, "expression": False}
3949
3950
3951class Sub(Binary):
3952    pass
3953
3954
3955class ArrayOverlaps(Binary):
3956    pass
3957
3958
3959# Unary Expressions
3960# (NOT a)
3961class Unary(Condition):
3962    pass
3963
3964
3965class BitwiseNot(Unary):
3966    pass
3967
3968
3969class Not(Unary):
3970    pass
3971
3972
3973class Paren(Unary):
3974    arg_types = {"this": True, "with": False}
3975
3976    @property
3977    def output_name(self) -> str:
3978        return self.this.name
3979
3980
3981class Neg(Unary):
3982    pass
3983
3984
3985class Alias(Expression):
3986    arg_types = {"this": True, "alias": False}
3987
3988    @property
3989    def output_name(self) -> str:
3990        return self.alias
3991
3992
3993class Aliases(Expression):
3994    arg_types = {"this": True, "expressions": True}
3995
3996    @property
3997    def aliases(self):
3998        return self.expressions
3999
4000
4001class AtTimeZone(Expression):
4002    arg_types = {"this": True, "zone": True}
4003
4004
4005class Between(Predicate):
4006    arg_types = {"this": True, "low": True, "high": True}
4007
4008
4009class Bracket(Condition):
4010    arg_types = {"this": True, "expressions": True}
4011
4012    @property
4013    def output_name(self) -> str:
4014        if len(self.expressions) == 1:
4015            return self.expressions[0].output_name
4016
4017        return super().output_name
4018
4019
4020class SafeBracket(Bracket):
4021    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4022
4023
4024class Distinct(Expression):
4025    arg_types = {"expressions": False, "on": False}
4026
4027
4028class In(Predicate):
4029    arg_types = {
4030        "this": True,
4031        "expressions": False,
4032        "query": False,
4033        "unnest": False,
4034        "field": False,
4035        "is_global": False,
4036    }
4037
4038
4039class TimeUnit(Expression):
4040    """Automatically converts unit arg into a var."""
4041
4042    arg_types = {"unit": False}
4043
4044    def __init__(self, **args):
4045        unit = args.get("unit")
4046        if isinstance(unit, (Column, Literal)):
4047            args["unit"] = Var(this=unit.name)
4048        elif isinstance(unit, Week):
4049            unit.set("this", Var(this=unit.this.name))
4050
4051        super().__init__(**args)
4052
4053    @property
4054    def unit(self) -> t.Optional[Var]:
4055        return self.args.get("unit")
4056
4057
4058class IntervalOp(TimeUnit):
4059    arg_types = {"unit": True, "expression": True}
4060
4061    def interval(self):
4062        return Interval(
4063            this=self.expression.copy(),
4064            unit=self.unit.copy(),
4065        )
4066
4067
4068# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4069# https://trino.io/docs/current/language/types.html#interval-day-to-second
4070# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4071class IntervalSpan(DataType):
4072    arg_types = {"this": True, "expression": True}
4073
4074
4075class Interval(TimeUnit):
4076    arg_types = {"this": False, "unit": False}
4077
4078
4079class IgnoreNulls(Expression):
4080    pass
4081
4082
4083class RespectNulls(Expression):
4084    pass
4085
4086
4087# Functions
4088class Func(Condition):
4089    """
4090    The base class for all function expressions.
4091
4092    Attributes:
4093        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4094            treated as a variable length argument and the argument's value will be stored as a list.
4095        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4096            for this function expression. These values are used to map this node to a name during parsing
4097            as well as to provide the function's name during SQL string generation. By default the SQL
4098            name is set to the expression's class name transformed to snake case.
4099    """
4100
4101    is_var_len_args = False
4102
4103    @classmethod
4104    def from_arg_list(cls, args):
4105        if cls.is_var_len_args:
4106            all_arg_keys = list(cls.arg_types)
4107            # If this function supports variable length argument treat the last argument as such.
4108            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4109            num_non_var = len(non_var_len_arg_keys)
4110
4111            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4112            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4113        else:
4114            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4115
4116        return cls(**args_dict)
4117
4118    @classmethod
4119    def sql_names(cls):
4120        if cls is Func:
4121            raise NotImplementedError(
4122                "SQL name is only supported by concrete function implementations"
4123            )
4124        if "_sql_names" not in cls.__dict__:
4125            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4126        return cls._sql_names
4127
4128    @classmethod
4129    def sql_name(cls):
4130        return cls.sql_names()[0]
4131
4132    @classmethod
4133    def default_parser_mappings(cls):
4134        return {name: cls.from_arg_list for name in cls.sql_names()}
4135
4136
4137class AggFunc(Func):
4138    pass
4139
4140
4141class ParameterizedAgg(AggFunc):
4142    arg_types = {"this": True, "expressions": True, "params": True}
4143
4144
4145class Abs(Func):
4146    pass
4147
4148
4149# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4150class Transform(Func):
4151    arg_types = {"this": True, "expression": True}
4152
4153
4154class Anonymous(Func):
4155    arg_types = {"this": True, "expressions": False}
4156    is_var_len_args = True
4157
4158
4159# https://docs.snowflake.com/en/sql-reference/functions/hll
4160# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4161class Hll(AggFunc):
4162    arg_types = {"this": True, "expressions": False}
4163    is_var_len_args = True
4164
4165
4166class ApproxDistinct(AggFunc):
4167    arg_types = {"this": True, "accuracy": False}
4168    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4169
4170
4171class Array(Func):
4172    arg_types = {"expressions": False}
4173    is_var_len_args = True
4174
4175
4176# https://docs.snowflake.com/en/sql-reference/functions/to_char
4177class ToChar(Func):
4178    arg_types = {"this": True, "format": False}
4179
4180
4181class GenerateSeries(Func):
4182    arg_types = {"start": True, "end": True, "step": False}
4183
4184
4185class ArrayAgg(AggFunc):
4186    pass
4187
4188
4189class ArrayAll(Func):
4190    arg_types = {"this": True, "expression": True}
4191
4192
4193class ArrayAny(Func):
4194    arg_types = {"this": True, "expression": True}
4195
4196
4197class ArrayConcat(Func):
4198    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4199    arg_types = {"this": True, "expressions": False}
4200    is_var_len_args = True
4201
4202
4203class ArrayContains(Binary, Func):
4204    pass
4205
4206
4207class ArrayContained(Binary):
4208    pass
4209
4210
4211class ArrayFilter(Func):
4212    arg_types = {"this": True, "expression": True}
4213    _sql_names = ["FILTER", "ARRAY_FILTER"]
4214
4215
4216class ArrayJoin(Func):
4217    arg_types = {"this": True, "expression": True, "null": False}
4218
4219
4220class ArraySize(Func):
4221    arg_types = {"this": True, "expression": False}
4222
4223
4224class ArraySort(Func):
4225    arg_types = {"this": True, "expression": False}
4226
4227
4228class ArraySum(Func):
4229    pass
4230
4231
4232class ArrayUnionAgg(AggFunc):
4233    pass
4234
4235
4236class Avg(AggFunc):
4237    pass
4238
4239
4240class AnyValue(AggFunc):
4241    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4242
4243
4244class First(Func):
4245    arg_types = {"this": True, "ignore_nulls": False}
4246
4247
4248class Last(Func):
4249    arg_types = {"this": True, "ignore_nulls": False}
4250
4251
4252class Case(Func):
4253    arg_types = {"this": False, "ifs": True, "default": False}
4254
4255    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4256        instance = maybe_copy(self, copy)
4257        instance.append(
4258            "ifs",
4259            If(
4260                this=maybe_parse(condition, copy=copy, **opts),
4261                true=maybe_parse(then, copy=copy, **opts),
4262            ),
4263        )
4264        return instance
4265
4266    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4267        instance = maybe_copy(self, copy)
4268        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4269        return instance
4270
4271
4272class Cast(Func):
4273    arg_types = {"this": True, "to": True, "format": False}
4274
4275    @property
4276    def name(self) -> str:
4277        return self.this.name
4278
4279    @property
4280    def to(self) -> DataType:
4281        return self.args["to"]
4282
4283    @property
4284    def output_name(self) -> str:
4285        return self.name
4286
4287    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4288        """
4289        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4290        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4291        array<int> != array<float>.
4292
4293        Args:
4294            dtypes: the data types to compare this Cast's DataType to.
4295
4296        Returns:
4297            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4298        """
4299        return self.to.is_type(*dtypes)
4300
4301
4302class TryCast(Cast):
4303    pass
4304
4305
4306class CastToStrType(Func):
4307    arg_types = {"this": True, "to": True}
4308
4309
4310class Collate(Binary, Func):
4311    pass
4312
4313
4314class Ceil(Func):
4315    arg_types = {"this": True, "decimals": False}
4316    _sql_names = ["CEIL", "CEILING"]
4317
4318
4319class Coalesce(Func):
4320    arg_types = {"this": True, "expressions": False}
4321    is_var_len_args = True
4322    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4323
4324
4325class Chr(Func):
4326    arg_types = {"this": True, "charset": False, "expressions": False}
4327    is_var_len_args = True
4328    _sql_names = ["CHR", "CHAR"]
4329
4330
4331class Concat(Func):
4332    arg_types = {"expressions": True}
4333    is_var_len_args = True
4334
4335
4336class SafeConcat(Concat):
4337    pass
4338
4339
4340class ConcatWs(Concat):
4341    _sql_names = ["CONCAT_WS"]
4342
4343
4344class Count(AggFunc):
4345    arg_types = {"this": False, "expressions": False}
4346    is_var_len_args = True
4347
4348
4349class CountIf(AggFunc):
4350    pass
4351
4352
4353class CurrentDate(Func):
4354    arg_types = {"this": False}
4355
4356
4357class CurrentDatetime(Func):
4358    arg_types = {"this": False}
4359
4360
4361class CurrentTime(Func):
4362    arg_types = {"this": False}
4363
4364
4365class CurrentTimestamp(Func):
4366    arg_types = {"this": False}
4367
4368
4369class CurrentUser(Func):
4370    arg_types = {"this": False}
4371
4372
4373class DateAdd(Func, IntervalOp):
4374    arg_types = {"this": True, "expression": True, "unit": False}
4375
4376
4377class DateSub(Func, IntervalOp):
4378    arg_types = {"this": True, "expression": True, "unit": False}
4379
4380
4381class DateDiff(Func, TimeUnit):
4382    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4383    arg_types = {"this": True, "expression": True, "unit": False}
4384
4385
4386class DateTrunc(Func):
4387    arg_types = {"unit": True, "this": True, "zone": False}
4388
4389    @property
4390    def unit(self) -> Expression:
4391        return self.args["unit"]
4392
4393
4394class DatetimeAdd(Func, IntervalOp):
4395    arg_types = {"this": True, "expression": True, "unit": False}
4396
4397
4398class DatetimeSub(Func, IntervalOp):
4399    arg_types = {"this": True, "expression": True, "unit": False}
4400
4401
4402class DatetimeDiff(Func, TimeUnit):
4403    arg_types = {"this": True, "expression": True, "unit": False}
4404
4405
4406class DatetimeTrunc(Func, TimeUnit):
4407    arg_types = {"this": True, "unit": True, "zone": False}
4408
4409
4410class DayOfWeek(Func):
4411    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4412
4413
4414class DayOfMonth(Func):
4415    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4416
4417
4418class DayOfYear(Func):
4419    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4420
4421
4422class ToDays(Func):
4423    pass
4424
4425
4426class WeekOfYear(Func):
4427    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4428
4429
4430class MonthsBetween(Func):
4431    arg_types = {"this": True, "expression": True, "roundoff": False}
4432
4433
4434class LastDateOfMonth(Func):
4435    pass
4436
4437
4438class Extract(Func):
4439    arg_types = {"this": True, "expression": True}
4440
4441
4442class Timestamp(Func):
4443    arg_types = {"this": False, "expression": False}
4444
4445
4446class TimestampAdd(Func, TimeUnit):
4447    arg_types = {"this": True, "expression": True, "unit": False}
4448
4449
4450class TimestampSub(Func, TimeUnit):
4451    arg_types = {"this": True, "expression": True, "unit": False}
4452
4453
4454class TimestampDiff(Func, TimeUnit):
4455    arg_types = {"this": True, "expression": True, "unit": False}
4456
4457
4458class TimestampTrunc(Func, TimeUnit):
4459    arg_types = {"this": True, "unit": True, "zone": False}
4460
4461
4462class TimeAdd(Func, TimeUnit):
4463    arg_types = {"this": True, "expression": True, "unit": False}
4464
4465
4466class TimeSub(Func, TimeUnit):
4467    arg_types = {"this": True, "expression": True, "unit": False}
4468
4469
4470class TimeDiff(Func, TimeUnit):
4471    arg_types = {"this": True, "expression": True, "unit": False}
4472
4473
4474class TimeTrunc(Func, TimeUnit):
4475    arg_types = {"this": True, "unit": True, "zone": False}
4476
4477
4478class DateFromParts(Func):
4479    _sql_names = ["DATEFROMPARTS"]
4480    arg_types = {"year": True, "month": True, "day": True}
4481
4482
4483class DateStrToDate(Func):
4484    pass
4485
4486
4487class DateToDateStr(Func):
4488    pass
4489
4490
4491class DateToDi(Func):
4492    pass
4493
4494
4495# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4496class Date(Func):
4497    arg_types = {"this": False, "zone": False, "expressions": False}
4498    is_var_len_args = True
4499
4500
4501class Day(Func):
4502    pass
4503
4504
4505class Decode(Func):
4506    arg_types = {"this": True, "charset": True, "replace": False}
4507
4508
4509class DiToDate(Func):
4510    pass
4511
4512
4513class Encode(Func):
4514    arg_types = {"this": True, "charset": True}
4515
4516
4517class Exp(Func):
4518    pass
4519
4520
4521class Explode(Func):
4522    pass
4523
4524
4525class Floor(Func):
4526    arg_types = {"this": True, "decimals": False}
4527
4528
4529class FromBase64(Func):
4530    pass
4531
4532
4533class ToBase64(Func):
4534    pass
4535
4536
4537class Greatest(Func):
4538    arg_types = {"this": True, "expressions": False}
4539    is_var_len_args = True
4540
4541
4542class GroupConcat(AggFunc):
4543    arg_types = {"this": True, "separator": False}
4544
4545
4546class Hex(Func):
4547    pass
4548
4549
4550class Xor(Connector, Func):
4551    arg_types = {"this": False, "expression": False, "expressions": False}
4552
4553
4554class If(Func):
4555    arg_types = {"this": True, "true": True, "false": False}
4556
4557
4558class Initcap(Func):
4559    arg_types = {"this": True, "expression": False}
4560
4561
4562class IsNan(Func):
4563    _sql_names = ["IS_NAN", "ISNAN"]
4564
4565
4566class FormatJson(Expression):
4567    pass
4568
4569
4570class JSONKeyValue(Expression):
4571    arg_types = {"this": True, "expression": True}
4572
4573
4574class JSONObject(Func):
4575    arg_types = {
4576        "expressions": False,
4577        "null_handling": False,
4578        "unique_keys": False,
4579        "return_type": False,
4580        "encoding": False,
4581    }
4582
4583
4584# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4585class JSONArray(Func):
4586    arg_types = {
4587        "expressions": True,
4588        "null_handling": False,
4589        "return_type": False,
4590        "strict": False,
4591    }
4592
4593
4594# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4595class JSONArrayAgg(Func):
4596    arg_types = {
4597        "this": True,
4598        "order": False,
4599        "null_handling": False,
4600        "return_type": False,
4601        "strict": False,
4602    }
4603
4604
4605# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4606# Note: parsing of JSON column definitions is currently incomplete.
4607class JSONColumnDef(Expression):
4608    arg_types = {"this": True, "kind": False, "path": False}
4609
4610
4611# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4612class JSONTable(Func):
4613    arg_types = {
4614        "this": True,
4615        "expressions": True,
4616        "path": False,
4617        "error_handling": False,
4618        "empty_handling": False,
4619    }
4620
4621
4622class OpenJSONColumnDef(Expression):
4623    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4624
4625
4626class OpenJSON(Func):
4627    arg_types = {"this": True, "path": False, "expressions": False}
4628
4629
4630class JSONBContains(Binary):
4631    _sql_names = ["JSONB_CONTAINS"]
4632
4633
4634class JSONExtract(Binary, Func):
4635    _sql_names = ["JSON_EXTRACT"]
4636
4637
4638class JSONExtractScalar(JSONExtract):
4639    _sql_names = ["JSON_EXTRACT_SCALAR"]
4640
4641
4642class JSONBExtract(JSONExtract):
4643    _sql_names = ["JSONB_EXTRACT"]
4644
4645
4646class JSONBExtractScalar(JSONExtract):
4647    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4648
4649
4650class JSONFormat(Func):
4651    arg_types = {"this": False, "options": False}
4652    _sql_names = ["JSON_FORMAT"]
4653
4654
4655# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4656class JSONArrayContains(Binary, Predicate, Func):
4657    _sql_names = ["JSON_ARRAY_CONTAINS"]
4658
4659
4660class ParseJSON(Func):
4661    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4662    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4663
4664
4665class Least(Func):
4666    arg_types = {"this": True, "expressions": False}
4667    is_var_len_args = True
4668
4669
4670class Left(Func):
4671    arg_types = {"this": True, "expression": True}
4672
4673
4674class Right(Func):
4675    arg_types = {"this": True, "expression": True}
4676
4677
4678class Length(Func):
4679    _sql_names = ["LENGTH", "LEN"]
4680
4681
4682class Levenshtein(Func):
4683    arg_types = {
4684        "this": True,
4685        "expression": False,
4686        "ins_cost": False,
4687        "del_cost": False,
4688        "sub_cost": False,
4689    }
4690
4691
4692class Ln(Func):
4693    pass
4694
4695
4696class Log(Func):
4697    arg_types = {"this": True, "expression": False}
4698
4699
4700class Log2(Func):
4701    pass
4702
4703
4704class Log10(Func):
4705    pass
4706
4707
4708class LogicalOr(AggFunc):
4709    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4710
4711
4712class LogicalAnd(AggFunc):
4713    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4714
4715
4716class Lower(Func):
4717    _sql_names = ["LOWER", "LCASE"]
4718
4719
4720class Map(Func):
4721    arg_types = {"keys": False, "values": False}
4722
4723
4724class MapFromEntries(Func):
4725    pass
4726
4727
4728class StarMap(Func):
4729    pass
4730
4731
4732class VarMap(Func):
4733    arg_types = {"keys": True, "values": True}
4734    is_var_len_args = True
4735
4736    @property
4737    def keys(self) -> t.List[Expression]:
4738        return self.args["keys"].expressions
4739
4740    @property
4741    def values(self) -> t.List[Expression]:
4742        return self.args["values"].expressions
4743
4744
4745# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4746class MatchAgainst(Func):
4747    arg_types = {"this": True, "expressions": True, "modifier": False}
4748
4749
4750class Max(AggFunc):
4751    arg_types = {"this": True, "expressions": False}
4752    is_var_len_args = True
4753
4754
4755class MD5(Func):
4756    _sql_names = ["MD5"]
4757
4758
4759# Represents the variant of the MD5 function that returns a binary value
4760class MD5Digest(Func):
4761    _sql_names = ["MD5_DIGEST"]
4762
4763
4764class Min(AggFunc):
4765    arg_types = {"this": True, "expressions": False}
4766    is_var_len_args = True
4767
4768
4769class Month(Func):
4770    pass
4771
4772
4773class Nvl2(Func):
4774    arg_types = {"this": True, "true": True, "false": False}
4775
4776
4777class Posexplode(Func):
4778    pass
4779
4780
4781# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4782class Predict(Func):
4783    arg_types = {"this": True, "expression": True, "params_struct": False}
4784
4785
4786class Pow(Binary, Func):
4787    _sql_names = ["POWER", "POW"]
4788
4789
4790class PercentileCont(AggFunc):
4791    arg_types = {"this": True, "expression": False}
4792
4793
4794class PercentileDisc(AggFunc):
4795    arg_types = {"this": True, "expression": False}
4796
4797
4798class Quantile(AggFunc):
4799    arg_types = {"this": True, "quantile": True}
4800
4801
4802class ApproxQuantile(Quantile):
4803    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4804
4805
4806class RangeN(Func):
4807    arg_types = {"this": True, "expressions": True, "each": False}
4808
4809
4810class ReadCSV(Func):
4811    _sql_names = ["READ_CSV"]
4812    is_var_len_args = True
4813    arg_types = {"this": True, "expressions": False}
4814
4815
4816class Reduce(Func):
4817    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4818
4819
4820class RegexpExtract(Func):
4821    arg_types = {
4822        "this": True,
4823        "expression": True,
4824        "position": False,
4825        "occurrence": False,
4826        "parameters": False,
4827        "group": False,
4828    }
4829
4830
4831class RegexpReplace(Func):
4832    arg_types = {
4833        "this": True,
4834        "expression": True,
4835        "replacement": True,
4836        "position": False,
4837        "occurrence": False,
4838        "parameters": False,
4839    }
4840
4841
4842class RegexpLike(Binary, Func):
4843    arg_types = {"this": True, "expression": True, "flag": False}
4844
4845
4846class RegexpILike(Func):
4847    arg_types = {"this": True, "expression": True, "flag": False}
4848
4849
4850# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4851# limit is the number of times a pattern is applied
4852class RegexpSplit(Func):
4853    arg_types = {"this": True, "expression": True, "limit": False}
4854
4855
4856class Repeat(Func):
4857    arg_types = {"this": True, "times": True}
4858
4859
4860class Round(Func):
4861    arg_types = {"this": True, "decimals": False}
4862
4863
4864class RowNumber(Func):
4865    arg_types: t.Dict[str, t.Any] = {}
4866
4867
4868class SafeDivide(Func):
4869    arg_types = {"this": True, "expression": True}
4870
4871
4872class SetAgg(AggFunc):
4873    pass
4874
4875
4876class SHA(Func):
4877    _sql_names = ["SHA", "SHA1"]
4878
4879
4880class SHA2(Func):
4881    _sql_names = ["SHA2"]
4882    arg_types = {"this": True, "length": False}
4883
4884
4885class SortArray(Func):
4886    arg_types = {"this": True, "asc": False}
4887
4888
4889class Split(Func):
4890    arg_types = {"this": True, "expression": True, "limit": False}
4891
4892
4893# Start may be omitted in the case of postgres
4894# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4895class Substring(Func):
4896    arg_types = {"this": True, "start": False, "length": False}
4897
4898
4899class StandardHash(Func):
4900    arg_types = {"this": True, "expression": False}
4901
4902
4903class StartsWith(Func):
4904    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4905    arg_types = {"this": True, "expression": True}
4906
4907
4908class StrPosition(Func):
4909    arg_types = {
4910        "this": True,
4911        "substr": True,
4912        "position": False,
4913        "instance": False,
4914    }
4915
4916
4917class StrToDate(Func):
4918    arg_types = {"this": True, "format": True}
4919
4920
4921class StrToTime(Func):
4922    arg_types = {"this": True, "format": True, "zone": False}
4923
4924
4925# Spark allows unix_timestamp()
4926# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4927class StrToUnix(Func):
4928    arg_types = {"this": False, "format": False}
4929
4930
4931# https://prestodb.io/docs/current/functions/string.html
4932# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
4933class StrToMap(Func):
4934    arg_types = {
4935        "this": True,
4936        "pair_delim": False,
4937        "key_value_delim": False,
4938        "duplicate_resolution_callback": False,
4939    }
4940
4941
4942class NumberToStr(Func):
4943    arg_types = {"this": True, "format": True, "culture": False}
4944
4945
4946class FromBase(Func):
4947    arg_types = {"this": True, "expression": True}
4948
4949
4950class Struct(Func):
4951    arg_types = {"expressions": True}
4952    is_var_len_args = True
4953
4954
4955class StructExtract(Func):
4956    arg_types = {"this": True, "expression": True}
4957
4958
4959# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
4960# https://docs.snowflake.com/en/sql-reference/functions/insert
4961class Stuff(Func):
4962    _sql_names = ["STUFF", "INSERT"]
4963    arg_types = {"this": True, "start": True, "length": True, "expression": True}
4964
4965
4966class Sum(AggFunc):
4967    pass
4968
4969
4970class Sqrt(Func):
4971    pass
4972
4973
4974class Stddev(AggFunc):
4975    pass
4976
4977
4978class StddevPop(AggFunc):
4979    pass
4980
4981
4982class StddevSamp(AggFunc):
4983    pass
4984
4985
4986class TimeToStr(Func):
4987    arg_types = {"this": True, "format": True, "culture": False}
4988
4989
4990class TimeToTimeStr(Func):
4991    pass
4992
4993
4994class TimeToUnix(Func):
4995    pass
4996
4997
4998class TimeStrToDate(Func):
4999    pass
5000
5001
5002class TimeStrToTime(Func):
5003    pass
5004
5005
5006class TimeStrToUnix(Func):
5007    pass
5008
5009
5010class Trim(Func):
5011    arg_types = {
5012        "this": True,
5013        "expression": False,
5014        "position": False,
5015        "collation": False,
5016    }
5017
5018
5019class TsOrDsAdd(Func, TimeUnit):
5020    arg_types = {"this": True, "expression": True, "unit": False}
5021
5022
5023class TsOrDsToDateStr(Func):
5024    pass
5025
5026
5027class TsOrDsToDate(Func):
5028    arg_types = {"this": True, "format": False}
5029
5030
5031class TsOrDiToDi(Func):
5032    pass
5033
5034
5035class Unhex(Func):
5036    pass
5037
5038
5039class UnixToStr(Func):
5040    arg_types = {"this": True, "format": False}
5041
5042
5043# https://prestodb.io/docs/current/functions/datetime.html
5044# presto has weird zone/hours/minutes
5045class UnixToTime(Func):
5046    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5047
5048    SECONDS = Literal.string("seconds")
5049    MILLIS = Literal.string("millis")
5050    MICROS = Literal.string("micros")
5051
5052
5053class UnixToTimeStr(Func):
5054    pass
5055
5056
5057class Upper(Func):
5058    _sql_names = ["UPPER", "UCASE"]
5059
5060
5061class Variance(AggFunc):
5062    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5063
5064
5065class VariancePop(AggFunc):
5066    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5067
5068
5069class Week(Func):
5070    arg_types = {"this": True, "mode": False}
5071
5072
5073class XMLTable(Func):
5074    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5075
5076
5077class Year(Func):
5078    pass
5079
5080
5081class Use(Expression):
5082    arg_types = {"this": True, "kind": False}
5083
5084
5085class Merge(Expression):
5086    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
5087
5088
5089class When(Func):
5090    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5091
5092
5093# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5094# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5095class NextValueFor(Func):
5096    arg_types = {"this": True, "order": False}
5097
5098
5099def _norm_arg(arg):
5100    return arg.lower() if type(arg) is str else arg
5101
5102
5103ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5104
5105
5106# Helpers
5107@t.overload
5108def maybe_parse(
5109    sql_or_expression: ExpOrStr,
5110    *,
5111    into: t.Type[E],
5112    dialect: DialectType = None,
5113    prefix: t.Optional[str] = None,
5114    copy: bool = False,
5115    **opts,
5116) -> E:
5117    ...
5118
5119
5120@t.overload
5121def maybe_parse(
5122    sql_or_expression: str | E,
5123    *,
5124    into: t.Optional[IntoType] = None,
5125    dialect: DialectType = None,
5126    prefix: t.Optional[str] = None,
5127    copy: bool = False,
5128    **opts,
5129) -> E:
5130    ...
5131
5132
5133def maybe_parse(
5134    sql_or_expression: ExpOrStr,
5135    *,
5136    into: t.Optional[IntoType] = None,
5137    dialect: DialectType = None,
5138    prefix: t.Optional[str] = None,
5139    copy: bool = False,
5140    **opts,
5141) -> Expression:
5142    """Gracefully handle a possible string or expression.
5143
5144    Example:
5145        >>> maybe_parse("1")
5146        (LITERAL this: 1, is_string: False)
5147        >>> maybe_parse(to_identifier("x"))
5148        (IDENTIFIER this: x, quoted: False)
5149
5150    Args:
5151        sql_or_expression: the SQL code string or an expression
5152        into: the SQLGlot Expression to parse into
5153        dialect: the dialect used to parse the input expressions (in the case that an
5154            input expression is a SQL string).
5155        prefix: a string to prefix the sql with before it gets parsed
5156            (automatically includes a space)
5157        copy: whether or not to copy the expression.
5158        **opts: other options to use to parse the input expressions (again, in the case
5159            that an input expression is a SQL string).
5160
5161    Returns:
5162        Expression: the parsed or given expression.
5163    """
5164    if isinstance(sql_or_expression, Expression):
5165        if copy:
5166            return sql_or_expression.copy()
5167        return sql_or_expression
5168
5169    if sql_or_expression is None:
5170        raise ParseError(f"SQL cannot be None")
5171
5172    import sqlglot
5173
5174    sql = str(sql_or_expression)
5175    if prefix:
5176        sql = f"{prefix} {sql}"
5177
5178    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5179
5180
5181@t.overload
5182def maybe_copy(instance: None, copy: bool = True) -> None:
5183    ...
5184
5185
5186@t.overload
5187def maybe_copy(instance: E, copy: bool = True) -> E:
5188    ...
5189
5190
5191def maybe_copy(instance, copy=True):
5192    return instance.copy() if copy and instance else instance
5193
5194
5195def _is_wrong_expression(expression, into):
5196    return isinstance(expression, Expression) and not isinstance(expression, into)
5197
5198
5199def _apply_builder(
5200    expression,
5201    instance,
5202    arg,
5203    copy=True,
5204    prefix=None,
5205    into=None,
5206    dialect=None,
5207    into_arg="this",
5208    **opts,
5209):
5210    if _is_wrong_expression(expression, into):
5211        expression = into(**{into_arg: expression})
5212    instance = maybe_copy(instance, copy)
5213    expression = maybe_parse(
5214        sql_or_expression=expression,
5215        prefix=prefix,
5216        into=into,
5217        dialect=dialect,
5218        **opts,
5219    )
5220    instance.set(arg, expression)
5221    return instance
5222
5223
5224def _apply_child_list_builder(
5225    *expressions,
5226    instance,
5227    arg,
5228    append=True,
5229    copy=True,
5230    prefix=None,
5231    into=None,
5232    dialect=None,
5233    properties=None,
5234    **opts,
5235):
5236    instance = maybe_copy(instance, copy)
5237    parsed = []
5238    for expression in expressions:
5239        if expression is not None:
5240            if _is_wrong_expression(expression, into):
5241                expression = into(expressions=[expression])
5242
5243            expression = maybe_parse(
5244                expression,
5245                into=into,
5246                dialect=dialect,
5247                prefix=prefix,
5248                **opts,
5249            )
5250            parsed.extend(expression.expressions)
5251
5252    existing = instance.args.get(arg)
5253    if append and existing:
5254        parsed = existing.expressions + parsed
5255
5256    child = into(expressions=parsed)
5257    for k, v in (properties or {}).items():
5258        child.set(k, v)
5259    instance.set(arg, child)
5260
5261    return instance
5262
5263
5264def _apply_list_builder(
5265    *expressions,
5266    instance,
5267    arg,
5268    append=True,
5269    copy=True,
5270    prefix=None,
5271    into=None,
5272    dialect=None,
5273    **opts,
5274):
5275    inst = maybe_copy(instance, copy)
5276
5277    expressions = [
5278        maybe_parse(
5279            sql_or_expression=expression,
5280            into=into,
5281            prefix=prefix,
5282            dialect=dialect,
5283            **opts,
5284        )
5285        for expression in expressions
5286        if expression is not None
5287    ]
5288
5289    existing_expressions = inst.args.get(arg)
5290    if append and existing_expressions:
5291        expressions = existing_expressions + expressions
5292
5293    inst.set(arg, expressions)
5294    return inst
5295
5296
5297def _apply_conjunction_builder(
5298    *expressions,
5299    instance,
5300    arg,
5301    into=None,
5302    append=True,
5303    copy=True,
5304    dialect=None,
5305    **opts,
5306):
5307    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5308    if not expressions:
5309        return instance
5310
5311    inst = maybe_copy(instance, copy)
5312
5313    existing = inst.args.get(arg)
5314    if append and existing is not None:
5315        expressions = [existing.this if into else existing] + list(expressions)
5316
5317    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5318
5319    inst.set(arg, into(this=node) if into else node)
5320    return inst
5321
5322
5323def _apply_cte_builder(
5324    instance: E,
5325    alias: ExpOrStr,
5326    as_: ExpOrStr,
5327    recursive: t.Optional[bool] = None,
5328    append: bool = True,
5329    dialect: DialectType = None,
5330    copy: bool = True,
5331    **opts,
5332) -> E:
5333    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5334    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5335    cte = CTE(this=as_expression, alias=alias_expression)
5336    return _apply_child_list_builder(
5337        cte,
5338        instance=instance,
5339        arg="with",
5340        append=append,
5341        copy=copy,
5342        into=With,
5343        properties={"recursive": recursive or False},
5344    )
5345
5346
5347def _combine(
5348    expressions: t.Sequence[t.Optional[ExpOrStr]],
5349    operator: t.Type[Connector],
5350    dialect: DialectType = None,
5351    copy: bool = True,
5352    **opts,
5353) -> Expression:
5354    conditions = [
5355        condition(expression, dialect=dialect, copy=copy, **opts)
5356        for expression in expressions
5357        if expression is not None
5358    ]
5359
5360    this, *rest = conditions
5361    if rest:
5362        this = _wrap(this, Connector)
5363    for expression in rest:
5364        this = operator(this=this, expression=_wrap(expression, Connector))
5365
5366    return this
5367
5368
5369def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5370    return Paren(this=expression) if isinstance(expression, kind) else expression
5371
5372
5373def union(
5374    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5375) -> Union:
5376    """
5377    Initializes a syntax tree from one UNION expression.
5378
5379    Example:
5380        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5381        'SELECT * FROM foo UNION SELECT * FROM bla'
5382
5383    Args:
5384        left: the SQL code string corresponding to the left-hand side.
5385            If an `Expression` instance is passed, it will be used as-is.
5386        right: the SQL code string corresponding to the right-hand side.
5387            If an `Expression` instance is passed, it will be used as-is.
5388        distinct: set the DISTINCT flag if and only if this is true.
5389        dialect: the dialect used to parse the input expression.
5390        opts: other options to use to parse the input expressions.
5391
5392    Returns:
5393        The new Union instance.
5394    """
5395    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5396    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5397
5398    return Union(this=left, expression=right, distinct=distinct)
5399
5400
5401def intersect(
5402    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5403) -> Intersect:
5404    """
5405    Initializes a syntax tree from one INTERSECT expression.
5406
5407    Example:
5408        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5409        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5410
5411    Args:
5412        left: the SQL code string corresponding to the left-hand side.
5413            If an `Expression` instance is passed, it will be used as-is.
5414        right: the SQL code string corresponding to the right-hand side.
5415            If an `Expression` instance is passed, it will be used as-is.
5416        distinct: set the DISTINCT flag if and only if this is true.
5417        dialect: the dialect used to parse the input expression.
5418        opts: other options to use to parse the input expressions.
5419
5420    Returns:
5421        The new Intersect instance.
5422    """
5423    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5424    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5425
5426    return Intersect(this=left, expression=right, distinct=distinct)
5427
5428
5429def except_(
5430    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5431) -> Except:
5432    """
5433    Initializes a syntax tree from one EXCEPT expression.
5434
5435    Example:
5436        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5437        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5438
5439    Args:
5440        left: the SQL code string corresponding to the left-hand side.
5441            If an `Expression` instance is passed, it will be used as-is.
5442        right: the SQL code string corresponding to the right-hand side.
5443            If an `Expression` instance is passed, it will be used as-is.
5444        distinct: set the DISTINCT flag if and only if this is true.
5445        dialect: the dialect used to parse the input expression.
5446        opts: other options to use to parse the input expressions.
5447
5448    Returns:
5449        The new Except instance.
5450    """
5451    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5452    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5453
5454    return Except(this=left, expression=right, distinct=distinct)
5455
5456
5457def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5458    """
5459    Initializes a syntax tree from one or multiple SELECT expressions.
5460
5461    Example:
5462        >>> select("col1", "col2").from_("tbl").sql()
5463        'SELECT col1, col2 FROM tbl'
5464
5465    Args:
5466        *expressions: the SQL code string to parse as the expressions of a
5467            SELECT statement. If an Expression instance is passed, this is used as-is.
5468        dialect: the dialect used to parse the input expressions (in the case that an
5469            input expression is a SQL string).
5470        **opts: other options to use to parse the input expressions (again, in the case
5471            that an input expression is a SQL string).
5472
5473    Returns:
5474        Select: the syntax tree for the SELECT statement.
5475    """
5476    return Select().select(*expressions, dialect=dialect, **opts)
5477
5478
5479def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5480    """
5481    Initializes a syntax tree from a FROM expression.
5482
5483    Example:
5484        >>> from_("tbl").select("col1", "col2").sql()
5485        'SELECT col1, col2 FROM tbl'
5486
5487    Args:
5488        *expression: the SQL code string to parse as the FROM expressions of a
5489            SELECT statement. If an Expression instance is passed, this is used as-is.
5490        dialect: the dialect used to parse the input expression (in the case that the
5491            input expression is a SQL string).
5492        **opts: other options to use to parse the input expressions (again, in the case
5493            that the input expression is a SQL string).
5494
5495    Returns:
5496        Select: the syntax tree for the SELECT statement.
5497    """
5498    return Select().from_(expression, dialect=dialect, **opts)
5499
5500
5501def update(
5502    table: str | Table,
5503    properties: dict,
5504    where: t.Optional[ExpOrStr] = None,
5505    from_: t.Optional[ExpOrStr] = None,
5506    dialect: DialectType = None,
5507    **opts,
5508) -> Update:
5509    """
5510    Creates an update statement.
5511
5512    Example:
5513        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5514        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5515
5516    Args:
5517        *properties: dictionary of properties to set which are
5518            auto converted to sql objects eg None -> NULL
5519        where: sql conditional parsed into a WHERE statement
5520        from_: sql statement parsed into a FROM statement
5521        dialect: the dialect used to parse the input expressions.
5522        **opts: other options to use to parse the input expressions.
5523
5524    Returns:
5525        Update: the syntax tree for the UPDATE statement.
5526    """
5527    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5528    update_expr.set(
5529        "expressions",
5530        [
5531            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5532            for k, v in properties.items()
5533        ],
5534    )
5535    if from_:
5536        update_expr.set(
5537            "from",
5538            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5539        )
5540    if isinstance(where, Condition):
5541        where = Where(this=where)
5542    if where:
5543        update_expr.set(
5544            "where",
5545            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5546        )
5547    return update_expr
5548
5549
5550def delete(
5551    table: ExpOrStr,
5552    where: t.Optional[ExpOrStr] = None,
5553    returning: t.Optional[ExpOrStr] = None,
5554    dialect: DialectType = None,
5555    **opts,
5556) -> Delete:
5557    """
5558    Builds a delete statement.
5559
5560    Example:
5561        >>> delete("my_table", where="id > 1").sql()
5562        'DELETE FROM my_table WHERE id > 1'
5563
5564    Args:
5565        where: sql conditional parsed into a WHERE statement
5566        returning: sql conditional parsed into a RETURNING statement
5567        dialect: the dialect used to parse the input expressions.
5568        **opts: other options to use to parse the input expressions.
5569
5570    Returns:
5571        Delete: the syntax tree for the DELETE statement.
5572    """
5573    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5574    if where:
5575        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5576    if returning:
5577        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5578    return delete_expr
5579
5580
5581def insert(
5582    expression: ExpOrStr,
5583    into: ExpOrStr,
5584    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5585    overwrite: t.Optional[bool] = None,
5586    dialect: DialectType = None,
5587    copy: bool = True,
5588    **opts,
5589) -> Insert:
5590    """
5591    Builds an INSERT statement.
5592
5593    Example:
5594        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5595        'INSERT INTO tbl VALUES (1, 2, 3)'
5596
5597    Args:
5598        expression: the sql string or expression of the INSERT statement
5599        into: the tbl to insert data to.
5600        columns: optionally the table's column names.
5601        overwrite: whether to INSERT OVERWRITE or not.
5602        dialect: the dialect used to parse the input expressions.
5603        copy: whether or not to copy the expression.
5604        **opts: other options to use to parse the input expressions.
5605
5606    Returns:
5607        Insert: the syntax tree for the INSERT statement.
5608    """
5609    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5610    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5611
5612    if columns:
5613        this = _apply_list_builder(
5614            *columns,
5615            instance=Schema(this=this),
5616            arg="expressions",
5617            into=Identifier,
5618            copy=False,
5619            dialect=dialect,
5620            **opts,
5621        )
5622
5623    return Insert(this=this, expression=expr, overwrite=overwrite)
5624
5625
5626def condition(
5627    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5628) -> Condition:
5629    """
5630    Initialize a logical condition expression.
5631
5632    Example:
5633        >>> condition("x=1").sql()
5634        'x = 1'
5635
5636        This is helpful for composing larger logical syntax trees:
5637        >>> where = condition("x=1")
5638        >>> where = where.and_("y=1")
5639        >>> Select().from_("tbl").select("*").where(where).sql()
5640        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5641
5642    Args:
5643        *expression: the SQL code string to parse.
5644            If an Expression instance is passed, this is used as-is.
5645        dialect: the dialect used to parse the input expression (in the case that the
5646            input expression is a SQL string).
5647        copy: Whether or not to copy `expression` (only applies to expressions).
5648        **opts: other options to use to parse the input expressions (again, in the case
5649            that the input expression is a SQL string).
5650
5651    Returns:
5652        The new Condition instance
5653    """
5654    return maybe_parse(
5655        expression,
5656        into=Condition,
5657        dialect=dialect,
5658        copy=copy,
5659        **opts,
5660    )
5661
5662
5663def and_(
5664    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5665) -> Condition:
5666    """
5667    Combine multiple conditions with an AND logical operator.
5668
5669    Example:
5670        >>> and_("x=1", and_("y=1", "z=1")).sql()
5671        'x = 1 AND (y = 1 AND z = 1)'
5672
5673    Args:
5674        *expressions: the SQL code strings to parse.
5675            If an Expression instance is passed, this is used as-is.
5676        dialect: the dialect used to parse the input expression.
5677        copy: whether or not to copy `expressions` (only applies to Expressions).
5678        **opts: other options to use to parse the input expressions.
5679
5680    Returns:
5681        And: the new condition
5682    """
5683    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5684
5685
5686def or_(
5687    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5688) -> Condition:
5689    """
5690    Combine multiple conditions with an OR logical operator.
5691
5692    Example:
5693        >>> or_("x=1", or_("y=1", "z=1")).sql()
5694        'x = 1 OR (y = 1 OR z = 1)'
5695
5696    Args:
5697        *expressions: the SQL code strings to parse.
5698            If an Expression instance is passed, this is used as-is.
5699        dialect: the dialect used to parse the input expression.
5700        copy: whether or not to copy `expressions` (only applies to Expressions).
5701        **opts: other options to use to parse the input expressions.
5702
5703    Returns:
5704        Or: the new condition
5705    """
5706    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5707
5708
5709def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5710    """
5711    Wrap a condition with a NOT operator.
5712
5713    Example:
5714        >>> not_("this_suit='black'").sql()
5715        "NOT this_suit = 'black'"
5716
5717    Args:
5718        expression: the SQL code string to parse.
5719            If an Expression instance is passed, this is used as-is.
5720        dialect: the dialect used to parse the input expression.
5721        copy: whether to copy the expression or not.
5722        **opts: other options to use to parse the input expressions.
5723
5724    Returns:
5725        The new condition.
5726    """
5727    this = condition(
5728        expression,
5729        dialect=dialect,
5730        copy=copy,
5731        **opts,
5732    )
5733    return Not(this=_wrap(this, Connector))
5734
5735
5736def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5737    """
5738    Wrap an expression in parentheses.
5739
5740    Example:
5741        >>> paren("5 + 3").sql()
5742        '(5 + 3)'
5743
5744    Args:
5745        expression: the SQL code string to parse.
5746            If an Expression instance is passed, this is used as-is.
5747        copy: whether to copy the expression or not.
5748
5749    Returns:
5750        The wrapped expression.
5751    """
5752    return Paren(this=maybe_parse(expression, copy=copy))
5753
5754
5755SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5756
5757
5758@t.overload
5759def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5760    ...
5761
5762
5763@t.overload
5764def to_identifier(
5765    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5766) -> Identifier:
5767    ...
5768
5769
5770def to_identifier(name, quoted=None, copy=True):
5771    """Builds an identifier.
5772
5773    Args:
5774        name: The name to turn into an identifier.
5775        quoted: Whether or not force quote the identifier.
5776        copy: Whether or not to copy a passed in Identefier node.
5777
5778    Returns:
5779        The identifier ast node.
5780    """
5781
5782    if name is None:
5783        return None
5784
5785    if isinstance(name, Identifier):
5786        identifier = maybe_copy(name, copy)
5787    elif isinstance(name, str):
5788        identifier = Identifier(
5789            this=name,
5790            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5791        )
5792    else:
5793        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5794    return identifier
5795
5796
5797INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5798
5799
5800def to_interval(interval: str | Literal) -> Interval:
5801    """Builds an interval expression from a string like '1 day' or '5 months'."""
5802    if isinstance(interval, Literal):
5803        if not interval.is_string:
5804            raise ValueError("Invalid interval string.")
5805
5806        interval = interval.this
5807
5808    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5809
5810    if not interval_parts:
5811        raise ValueError("Invalid interval string.")
5812
5813    return Interval(
5814        this=Literal.string(interval_parts.group(1)),
5815        unit=Var(this=interval_parts.group(2)),
5816    )
5817
5818
5819@t.overload
5820def to_table(sql_path: str | Table, **kwargs) -> Table:
5821    ...
5822
5823
5824@t.overload
5825def to_table(sql_path: None, **kwargs) -> None:
5826    ...
5827
5828
5829def to_table(
5830    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5831) -> t.Optional[Table]:
5832    """
5833    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5834    If a table is passed in then that table is returned.
5835
5836    Args:
5837        sql_path: a `[catalog].[schema].[table]` string.
5838        dialect: the source dialect according to which the table name will be parsed.
5839        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5840
5841    Returns:
5842        A table expression.
5843    """
5844    if sql_path is None or isinstance(sql_path, Table):
5845        return sql_path
5846    if not isinstance(sql_path, str):
5847        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5848
5849    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5850    if table:
5851        for k, v in kwargs.items():
5852            table.set(k, v)
5853
5854    return table
5855
5856
5857def to_column(sql_path: str | Column, **kwargs) -> Column:
5858    """
5859    Create a column from a `[table].[column]` sql path. Schema is optional.
5860
5861    If a column is passed in then that column is returned.
5862
5863    Args:
5864        sql_path: `[table].[column]` string
5865    Returns:
5866        Table: A column expression
5867    """
5868    if sql_path is None or isinstance(sql_path, Column):
5869        return sql_path
5870    if not isinstance(sql_path, str):
5871        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5872    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5873
5874
5875def alias_(
5876    expression: ExpOrStr,
5877    alias: str | Identifier,
5878    table: bool | t.Sequence[str | Identifier] = False,
5879    quoted: t.Optional[bool] = None,
5880    dialect: DialectType = None,
5881    copy: bool = True,
5882    **opts,
5883):
5884    """Create an Alias expression.
5885
5886    Example:
5887        >>> alias_('foo', 'bar').sql()
5888        'foo AS bar'
5889
5890        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5891        '(SELECT 1, 2) AS bar(a, b)'
5892
5893    Args:
5894        expression: the SQL code strings to parse.
5895            If an Expression instance is passed, this is used as-is.
5896        alias: the alias name to use. If the name has
5897            special characters it is quoted.
5898        table: Whether or not to create a table alias, can also be a list of columns.
5899        quoted: whether or not to quote the alias
5900        dialect: the dialect used to parse the input expression.
5901        copy: Whether or not to copy the expression.
5902        **opts: other options to use to parse the input expressions.
5903
5904    Returns:
5905        Alias: the aliased expression
5906    """
5907    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5908    alias = to_identifier(alias, quoted=quoted)
5909
5910    if table:
5911        table_alias = TableAlias(this=alias)
5912        exp.set("alias", table_alias)
5913
5914        if not isinstance(table, bool):
5915            for column in table:
5916                table_alias.append("columns", to_identifier(column, quoted=quoted))
5917
5918        return exp
5919
5920    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
5921    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
5922    # for the complete Window expression.
5923    #
5924    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
5925
5926    if "alias" in exp.arg_types and not isinstance(exp, Window):
5927        exp.set("alias", alias)
5928        return exp
5929    return Alias(this=exp, alias=alias)
5930
5931
5932def subquery(
5933    expression: ExpOrStr,
5934    alias: t.Optional[Identifier | str] = None,
5935    dialect: DialectType = None,
5936    **opts,
5937) -> Select:
5938    """
5939    Build a subquery expression.
5940
5941    Example:
5942        >>> subquery('select x from tbl', 'bar').select('x').sql()
5943        'SELECT x FROM (SELECT x FROM tbl) AS bar'
5944
5945    Args:
5946        expression: the SQL code strings to parse.
5947            If an Expression instance is passed, this is used as-is.
5948        alias: the alias name to use.
5949        dialect: the dialect used to parse the input expression.
5950        **opts: other options to use to parse the input expressions.
5951
5952    Returns:
5953        A new Select instance with the subquery expression included.
5954    """
5955
5956    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
5957    return Select().from_(expression, dialect=dialect, **opts)
5958
5959
5960def column(
5961    col: str | Identifier,
5962    table: t.Optional[str | Identifier] = None,
5963    db: t.Optional[str | Identifier] = None,
5964    catalog: t.Optional[str | Identifier] = None,
5965    quoted: t.Optional[bool] = None,
5966) -> Column:
5967    """
5968    Build a Column.
5969
5970    Args:
5971        col: Column name.
5972        table: Table name.
5973        db: Database name.
5974        catalog: Catalog name.
5975        quoted: Whether to force quotes on the column's identifiers.
5976
5977    Returns:
5978        The new Column instance.
5979    """
5980    return Column(
5981        this=to_identifier(col, quoted=quoted),
5982        table=to_identifier(table, quoted=quoted),
5983        db=to_identifier(db, quoted=quoted),
5984        catalog=to_identifier(catalog, quoted=quoted),
5985    )
5986
5987
5988def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
5989    """Cast an expression to a data type.
5990
5991    Example:
5992        >>> cast('x + 1', 'int').sql()
5993        'CAST(x + 1 AS INT)'
5994
5995    Args:
5996        expression: The expression to cast.
5997        to: The datatype to cast to.
5998
5999    Returns:
6000        The new Cast instance.
6001    """
6002    expression = maybe_parse(expression, **opts)
6003    data_type = DataType.build(to, **opts)
6004    expression = Cast(this=expression, to=data_type)
6005    expression.type = data_type
6006    return expression
6007
6008
6009def table_(
6010    table: Identifier | str,
6011    db: t.Optional[Identifier | str] = None,
6012    catalog: t.Optional[Identifier | str] = None,
6013    quoted: t.Optional[bool] = None,
6014    alias: t.Optional[Identifier | str] = None,
6015) -> Table:
6016    """Build a Table.
6017
6018    Args:
6019        table: Table name.
6020        db: Database name.
6021        catalog: Catalog name.
6022        quote: Whether to force quotes on the table's identifiers.
6023        alias: Table's alias.
6024
6025    Returns:
6026        The new Table instance.
6027    """
6028    return Table(
6029        this=to_identifier(table, quoted=quoted) if table else None,
6030        db=to_identifier(db, quoted=quoted) if db else None,
6031        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6032        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6033    )
6034
6035
6036def values(
6037    values: t.Iterable[t.Tuple[t.Any, ...]],
6038    alias: t.Optional[str] = None,
6039    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6040) -> Values:
6041    """Build VALUES statement.
6042
6043    Example:
6044        >>> values([(1, '2')]).sql()
6045        "VALUES (1, '2')"
6046
6047    Args:
6048        values: values statements that will be converted to SQL
6049        alias: optional alias
6050        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6051         If either are provided then an alias is also required.
6052
6053    Returns:
6054        Values: the Values expression object
6055    """
6056    if columns and not alias:
6057        raise ValueError("Alias is required when providing columns")
6058
6059    return Values(
6060        expressions=[convert(tup) for tup in values],
6061        alias=(
6062            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6063            if columns
6064            else (TableAlias(this=to_identifier(alias)) if alias else None)
6065        ),
6066    )
6067
6068
6069def var(name: t.Optional[ExpOrStr]) -> Var:
6070    """Build a SQL variable.
6071
6072    Example:
6073        >>> repr(var('x'))
6074        '(VAR this: x)'
6075
6076        >>> repr(var(column('x', table='y')))
6077        '(VAR this: x)'
6078
6079    Args:
6080        name: The name of the var or an expression who's name will become the var.
6081
6082    Returns:
6083        The new variable node.
6084    """
6085    if not name:
6086        raise ValueError("Cannot convert empty name into var.")
6087
6088    if isinstance(name, Expression):
6089        name = name.name
6090    return Var(this=name)
6091
6092
6093def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6094    """Build ALTER TABLE... RENAME... expression
6095
6096    Args:
6097        old_name: The old name of the table
6098        new_name: The new name of the table
6099
6100    Returns:
6101        Alter table expression
6102    """
6103    old_table = to_table(old_name)
6104    new_table = to_table(new_name)
6105    return AlterTable(
6106        this=old_table,
6107        actions=[
6108            RenameTable(this=new_table),
6109        ],
6110    )
6111
6112
6113def convert(value: t.Any, copy: bool = False) -> Expression:
6114    """Convert a python value into an expression object.
6115
6116    Raises an error if a conversion is not possible.
6117
6118    Args:
6119        value: A python object.
6120        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6121
6122    Returns:
6123        Expression: the equivalent expression object.
6124    """
6125    if isinstance(value, Expression):
6126        return maybe_copy(value, copy)
6127    if isinstance(value, str):
6128        return Literal.string(value)
6129    if isinstance(value, bool):
6130        return Boolean(this=value)
6131    if value is None or (isinstance(value, float) and math.isnan(value)):
6132        return NULL
6133    if isinstance(value, numbers.Number):
6134        return Literal.number(value)
6135    if isinstance(value, datetime.datetime):
6136        datetime_literal = Literal.string(
6137            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6138        )
6139        return TimeStrToTime(this=datetime_literal)
6140    if isinstance(value, datetime.date):
6141        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6142        return DateStrToDate(this=date_literal)
6143    if isinstance(value, tuple):
6144        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6145    if isinstance(value, list):
6146        return Array(expressions=[convert(v, copy=copy) for v in value])
6147    if isinstance(value, dict):
6148        return Map(
6149            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6150            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6151        )
6152    raise ValueError(f"Cannot convert {value}")
6153
6154
6155def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6156    """
6157    Replace children of an expression with the result of a lambda fun(child) -> exp.
6158    """
6159    for k, v in expression.args.items():
6160        is_list_arg = type(v) is list
6161
6162        child_nodes = v if is_list_arg else [v]
6163        new_child_nodes = []
6164
6165        for cn in child_nodes:
6166            if isinstance(cn, Expression):
6167                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6168                    new_child_nodes.append(child_node)
6169                    child_node.parent = expression
6170                    child_node.arg_key = k
6171            else:
6172                new_child_nodes.append(cn)
6173
6174        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6175
6176
6177def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6178    """
6179    Return all table names referenced through columns in an expression.
6180
6181    Example:
6182        >>> import sqlglot
6183        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6184        ['a', 'c']
6185
6186    Args:
6187        expression: expression to find table names.
6188        exclude: a table name to exclude
6189
6190    Returns:
6191        A list of unique names.
6192    """
6193    return {
6194        table
6195        for table in (column.table for column in expression.find_all(Column))
6196        if table and table != exclude
6197    }
6198
6199
6200def table_name(table: Table | str, dialect: DialectType = None) -> str:
6201    """Get the full name of a table as a string.
6202
6203    Args:
6204        table: Table expression node or string.
6205        dialect: The dialect to generate the table name for.
6206
6207    Examples:
6208        >>> from sqlglot import exp, parse_one
6209        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6210        'a.b.c'
6211
6212    Returns:
6213        The table name.
6214    """
6215
6216    table = maybe_parse(table, into=Table, dialect=dialect)
6217
6218    if not table:
6219        raise ValueError(f"Cannot parse {table}")
6220
6221    return ".".join(
6222        part.sql(dialect=dialect, identify=True)
6223        if not SAFE_IDENTIFIER_RE.match(part.name)
6224        else part.name
6225        for part in table.parts
6226    )
6227
6228
6229def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6230    """Replace all tables in expression according to the mapping.
6231
6232    Args:
6233        expression: expression node to be transformed and replaced.
6234        mapping: mapping of table names.
6235        copy: whether or not to copy the expression.
6236
6237    Examples:
6238        >>> from sqlglot import exp, parse_one
6239        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6240        'SELECT * FROM c'
6241
6242    Returns:
6243        The mapped expression.
6244    """
6245
6246    def _replace_tables(node: Expression) -> Expression:
6247        if isinstance(node, Table):
6248            new_name = mapping.get(table_name(node))
6249            if new_name:
6250                return to_table(
6251                    new_name,
6252                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6253                )
6254        return node
6255
6256    return expression.transform(_replace_tables, copy=copy)
6257
6258
6259def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6260    """Replace placeholders in an expression.
6261
6262    Args:
6263        expression: expression node to be transformed and replaced.
6264        args: positional names that will substitute unnamed placeholders in the given order.
6265        kwargs: keyword arguments that will substitute named placeholders.
6266
6267    Examples:
6268        >>> from sqlglot import exp, parse_one
6269        >>> replace_placeholders(
6270        ...     parse_one("select * from :tbl where ? = ?"),
6271        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6272        ... ).sql()
6273        "SELECT * FROM foo WHERE str_col = 'b'"
6274
6275    Returns:
6276        The mapped expression.
6277    """
6278
6279    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6280        if isinstance(node, Placeholder):
6281            if node.name:
6282                new_name = kwargs.get(node.name)
6283                if new_name:
6284                    return convert(new_name)
6285            else:
6286                try:
6287                    return convert(next(args))
6288                except StopIteration:
6289                    pass
6290        return node
6291
6292    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6293
6294
6295def expand(
6296    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6297) -> Expression:
6298    """Transforms an expression by expanding all referenced sources into subqueries.
6299
6300    Examples:
6301        >>> from sqlglot import parse_one
6302        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6303        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6304
6305        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6306        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6307
6308    Args:
6309        expression: The expression to expand.
6310        sources: A dictionary of name to Subqueryables.
6311        copy: Whether or not to copy the expression during transformation. Defaults to True.
6312
6313    Returns:
6314        The transformed expression.
6315    """
6316
6317    def _expand(node: Expression):
6318        if isinstance(node, Table):
6319            name = table_name(node)
6320            source = sources.get(name)
6321            if source:
6322                subquery = source.subquery(node.alias or name)
6323                subquery.comments = [f"source: {name}"]
6324                return subquery.transform(_expand, copy=False)
6325        return node
6326
6327    return expression.transform(_expand, copy=copy)
6328
6329
6330def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6331    """
6332    Returns a Func expression.
6333
6334    Examples:
6335        >>> func("abs", 5).sql()
6336        'ABS(5)'
6337
6338        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6339        'CAST(5 AS DOUBLE)'
6340
6341    Args:
6342        name: the name of the function to build.
6343        args: the args used to instantiate the function of interest.
6344        dialect: the source dialect.
6345        kwargs: the kwargs used to instantiate the function of interest.
6346
6347    Note:
6348        The arguments `args` and `kwargs` are mutually exclusive.
6349
6350    Returns:
6351        An instance of the function of interest, or an anonymous function, if `name` doesn't
6352        correspond to an existing `sqlglot.expressions.Func` class.
6353    """
6354    if args and kwargs:
6355        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6356
6357    from sqlglot.dialects.dialect import Dialect
6358
6359    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6360    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6361
6362    parser = Dialect.get_or_raise(dialect)().parser()
6363    from_args_list = parser.FUNCTIONS.get(name.upper())
6364
6365    if from_args_list:
6366        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6367    else:
6368        kwargs = kwargs or {"expressions": converted}
6369        function = Anonymous(this=name, **kwargs)
6370
6371    for error_message in function.error_messages(converted):
6372        raise ValueError(error_message)
6373
6374    return function
6375
6376
6377def true() -> Boolean:
6378    """
6379    Returns a true Boolean expression.
6380    """
6381    return Boolean(this=True)
6382
6383
6384def false() -> Boolean:
6385    """
6386    Returns a false Boolean expression.
6387    """
6388    return Boolean(this=False)
6389
6390
6391def null() -> Null:
6392    """
6393    Returns a Null expression.
6394    """
6395    return Null()
6396
6397
6398# TODO: deprecate this
6399TRUE = Boolean(this=True)
6400FALSE = Boolean(this=False)
6401NULL = Null()